CODEWITHSUNDEEP
sql
Dustin Sun
Dustin Sun

Reputation: 5532

Priority Search - SQL

Consider a user enters a keyword to search for books. First the application searches by book title matches, if more than 50 books matches the search then it returns results; if not, it searches by book author matches. If together no more than 50 books matches, if then searches by book description matches.

Can SQL handle this situation? Or I have to write a stored procedure with ifs and elseifs?

[added] I use MySQL5.6. But I feel like the answer lies in in ANSI-SQL so I didn't specify it. Many thanks for your help!

Upvotes: 0

Views: 1173

Answers (2)

Ionic
Ionic

Reputation: 3935

You can realize it using union all with subqueries.

SELECT DISTINCT TOP(50) bookname
FROM (
    SELECT TOP(50) bookname, 1 as rank
    FROM yourTable
    WHERE bookTitle = N'BLA'
    UNION
    SELECT TOP(50) bookname, 2 as rank
    FROM yourTable
    WHERE bookAuthor = N'BLA'
    UNION
    SELECT TOP(50) bookname, 3 as rank
    FROM yourTable
    WHERE bookDescription = N'BLA'
) as data
ORDER BY rank

This works on SQL Server. On MySQL you need to use LIMIT instead of TOP. You should specify which server you use in your tags.

EDIT (by other user):

This is an accepted answer, but it has a problem because it might return duplicates. A book that matches all three conditions can be returned three times. The union doesn't eliminate them, because the rank is different on each row.

One solution is to remove the rank and use a case statement in the order by:

order by (case when bookTitle = N'BLA' then 1
               when bookAuthor = N'BLA' then 2
          end)

EDIT by author: I changed the code above using the defined column names and a distinct, which will preserve duplicate entries.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269633

One approach to this is to do all the comparisons using a single query and then prioritize the results. The ANSI standard syntax would be like this:

select b.*
from books b
where Title . . . or
      Author . . . or
      Description . . . 
order by (case when Title . . . then 1
               when Author . . . then 2
               when Description . . then 3
          end)
fetch first 50 rows only

Of course, the final clause is not supported in all databases, so it might be limit or top or something even worse.

If you have a large table, and performance is an issue, there are other ways to phrase the query, depending on what the matching conditions look like.

Upvotes: 3

Related Questions