Is it possible to build the following SQL query

Question

The original query looks like this (MySQL):

SELECT * FROM books WHERE title LIKE "%text%" OR description LIKE "%text%" ORDER BY date 

Is it possible to rewrite it (without unions or procedures) so that the result looks like this:

• a list of books in which the title corresponds to a query sorted by date, and then:
• a list of books in which the description matches a query sorted by date

So basically just give higher priority to matching titles with descriptions.

Answer 1

In sql server I would do the following:

 select * from books where title like '%text%' or description like '%text%' order by case when title like '%text%' then 1 else 2 end, date 

I'm not sure if you can include columns in ORDER BY in mysql that are not in SELECT, but I would use this principle. Otherwise, just include the derived column in SELECT.

Answer 2

 select * from books where title like "%text%" or description like "%text%" order by date, case when title like "%text%" then 0 else 1 end 

Answer 3

Sentence

rjk is the right way. Keep in mind, however, that this query (with or without union) cannot use indexes, so it will not scale well. You might want to check out MySQL's full-text indexing, which will scale better, resolve more complex queries, and even help with ranking results.

Answer 4

You can use case to sort by:

 order by case when title like '%text%' then 0 else 1 end 

Answer 5

How about something like that ...

 select * from books where title like "%text%" or description like "%text%" order by case when title like "%text%" then 1 else 0 end desc, date 

Answer 6

 DECLARE @Books TABLE ( [ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY, [Title] NVARCHAR(MAX) NOT NULL, [Description] NVARCHAR(MAX) NOT NULL, [Date] DATETIME NOT NULL ) INSERT INTO @Books SELECT 'War and Peace','A Russian Epic','2008-01-01' UNION SELECT 'Dogs of War','Mercenary Stories','2006-01-01' UNION SELECT 'World At Arms','A Story of World War Two','2007-01-01' UNION SELECT 'The B Team','Street Wars','2005-01-01' SELECT * FROM ( SELECT *, CASE WHEN [Title] LIKE '%war%' THEN 1 WHEN [Description] LIKE '%war%' THEN 2 END AS Ord FROM @Books WHERE [Title] LIKE '%war%' OR [Description] LIKE '%war%' ) AS Derived ORDER BY Ord ASC, [Date] ASC 

I believe this gives you what you want, but due to the extra workload in CASE derived status, it may not have good performance.

Answer 7

The union command will help you. Something like that:

 SELECT *, 1 as order from books where title like '%text%' union SELECT *, 2 as order from books where description like '%text%' ORDER BY order, date