SEO

#1 (**permalink**) 02-29-2008, 08:31 PM

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
dcousineau@gmail.com

#2 (**permalink**) 02-29-2008, 08:31 PM

dcousin...@gmail.com 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
> dcousineau@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.

#3 (**permalink**) 03-01-2008, 11:20 AM

Thanks!

ZeldorBlat wrote:
> dcousin...@gmail.com 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
> > dcousineau@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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Searching problem