|
|
Posted by ZeldorBlat on 09/25/13 11:59
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.
Navigation:
[Reply to this message]
|