return search by relevancy? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

return search by relevancy?

Author

Topic

Kafer
Starting Member

3 Posts

Posted - 2004-06-08 : 02:40:25

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?

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-08 : 02:47:24

SELECT TitleName, 1 as Rank WHERE Titlename = 'Crimson Skies' order BY Titlename
UNION
SELECT TitleName, 2 as Rank WHERE Titlename LIKE '%Crimson%' AND Titlename LIKE '%Skies%' order BY Titlename

You can use the 'Rank' field to order the output so that the close matches come first, then the near matches.
The only drawback is that you'll see duplicates in the second set of data. You can remove the rank, and Access/SQL will return a distinct set of results.

Tim

btw - Access uses * for it's wildcard instead of % (which is SQL Server)

Kafer
Starting Member

3 Posts

Posted - 2004-06-09 : 03:55:03

Thanks, Timmy! That's works great when I order by 1 and then by 2.

However, as you mentioned, I get duplicates which is a head-scratcher since UNION is supposed to eliminate dupes. Any idea how to get rid of these dupes?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-06-09 : 05:55:33

have a look at "UNION ALL"

Kafer
Starting Member

3 Posts

Posted - 2004-06-10 : 02:09:21

UNION ALL keeps the dupes, which is what I'm trying to avoid.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-10 : 02:48:19

If you want to eliminate the dupe's, you'll have to do without the Rank field.

Alternatively, your front-end could do the filtering for you.