Searching problem

[dcousineau]

Hi! I'm working with a database of news clippings. The database has
fields for the title of the article and the text of the article (also
other fields for things like sources and categories, but those have no
relevance to the question).

We have a single search box that normally searches both the title field
and the body text field (with wildcard characters: '%SEARCH_STRING%').

What I want is for the search results to be ordered so that matches in
the title field come first (and this section is ordered by an ORDER
clause, i.e. by Date or by topic, etc.) then matches from the body text
field (again this section ordered by an ORDER clause).

If this is possible, I'd like to know if this can all be rolled into a
single SQL statement so that ADO through ASP can handle all the paging
for me. I know this isn't the listerv for it but if this can't be
rolled into a single SQL statement, does anyone here happen to know ASP
(in VB Script, no C#...)/ADO well enough to tackle the problem?

Thanks guys!

Daniel Cousineau
dc********@gmail.com

[ZeldorBlat]

[e-mail] wrote:

Hi! I'm working with a database of news clippings. The database has
fields for the title of the article and the text of the article (also
other fields for things like sources and categories, but those have no
relevance to the question).

We have a single search box that normally searches both the title field
and the body text field (with wildcard characters: '%SEARCH_STRING%').

What I want is for the search results to be ordered so that matches in
the title field come first (and this section is ordered by an ORDER
clause, i.e. by Date or by topic, etc.) then matches from the body text
field (again this section ordered by an ORDER clause).

If this is possible, I'd like to know if this can all be rolled into a
single SQL statement so that ADO through ASP can handle all the paging
for me. I know this isn't the listerv for it but if this can't be
rolled into a single SQL statement, does anyone here happen to know ASP
(in VB Script, no C#...)/ADO well enough to tackle the problem?

Thanks guys!

Daniel Cousineau
dc********@gmail.com

Just order it by some statement that returns either 1 or 0 depending on
where the match is. "case" should do the trick:

select ...
from someTable
where (headline like '%searchString%' or body like '%searchString%')
order by (case when headline like '%searchString%' then 0
else 1 end), postDate, topicName, etc.

[dcousineau]

Thanks!

ZeldorBlat wrote:

[e-mail] wrote:

Hi! I'm working with a database of news clippings. The database has
fields for the title of the article and the text of the article (also
other fields for things like sources and categories, but those have no
relevance to the question).

We have a single search box that normally searches both the title field
and the body text field (with wildcard characters: '%SEARCH_STRING%').

What I want is for the search results to be ordered so that matches in
the title field come first (and this section is ordered by an ORDER
clause, i.e. by Date or by topic, etc.) then matches from the body text
field (again this section ordered by an ORDER clause).

If this is possible, I'd like to know if this can all be rolled into a
single SQL statement so that ADO through ASP can handle all the paging
for me. I know this isn't the listerv for it but if this can't be
rolled into a single SQL statement, does anyone here happen to know ASP
(in VB Script, no C#...)/ADO well enough to tackle the problem?

Thanks guys!

Daniel Cousineau
dc********@gmail.com

Just order it by some statement that returns either 1 or 0 depending on
where the match is. "case" should do the trick:

select ...
from someTable
where (headline like '%searchString%' or body like '%searchString%')
order by (case when headline like '%searchString%' then 0
else 1 end), postDate, topicName, etc.