Order recordsets by relevancy

Kafer · June 8th, 2004, 02:30 AM

I have an SQL string that does a search using

SELECT * WHERE Titlename LIKE '%Crimson%' AND Titlename LIKE '%Skies%' order BY Titlename

This gives me all results containing both "Crimson" and "Skies". But I want a result that contains exactly "Crimson Skies" to appear first in addition to the blended results. Any idea how?

happygv · June 8th, 2004, 02:56 AM

Then this may help you.

Code:

SELECT * WHERE Titlename LIKE '%Crimson Skies%' or (Titlename NOT LIKE '%Crimson Skies%' and LIKE '%Crimson%Skies%') order BY Titlename

Cheers!

_________________________
-Vijay G

Strive for Perfection

Kafer · June 8th, 2004, 03:42 AM

Hmmm, still doesn't work, as exact matches for "Crimson Skies" are still not at the top. I'm not quite sure what your logic was for this query, so if you can explain it, maybe that'll kick-start something in my brain. Thanks!

om_prakash · June 8th, 2004, 03:50 AM

Hi,

you can try thr following query:

SELECT * WHERE Titlename LIKE '%Crimson Skies%' order BY Titlename

Om Prakash

happygv · June 9th, 2004, 12:27 AM

Hi Kafer,
Then you will have to split that in to two queries and use UNION Operator.

Code:

SELECT * WHERE Titlename LIKE '%Crimson Skies%'
SELECT * WHERE Titlename LIKE Titlename NOT LIKE '%Crimson Skies%' and LIKE '%Crimson%Skies%'
UNION ALL
order BY Titlename

Use the Order by Clause if required, because this would again collapes the order in which you want it to be displayed.

Hope that helps.
Cheers!

_________________________
-Vijay G

Strive for Perfection

Kafer · June 9th, 2004, 03:02 AM

Actually, the best solution I found so far is

SELECT Titlename, 1 as rank WHERE Titlename like '%Crimson Skies%' group by Titlename order by 1
UNION
SELECT Titlename, 2 as rank WHERE Titlename LIKE '%Crimson%' AND Titlename LIKE '%Skies%' group by Titlename order by 2

But this gives me duplicates. Any idea how to get rid of the dupes?

happygv · June 9th, 2004, 03:09 AM

Code:

Select distinct Title from
(
SELECT Titlename, 1 as rank WHERE Titlename like '%Crimson Skies%' group by Titlename order by 1 
UNION 
SELECT Titlename, 2 as rank WHERE Titlename LIKE '%Crimson%' AND Titlename LIKE '%Skies%'  group by Titlename order by 2 
)

Does that help?
Cheers!

_________________________
-Vijay G

Strive for Perfection