Is it possible to build 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

Would it be possible to rewrite it (without unions or procedures), so that result will look like this: - list of books where title matches query ordered by date, followed by: - list of books where description matches query ordered by date

So basically just give a higher priority to matching titles over descriptions.

Thanks.

Answer 1

The union command will help you. Something along these lines:

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

Answer 2

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 aren't in the SELECT, but that's the principle I'd use. Otherwise, just include the derived column in the SELECT as well.

Answer 3

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 4

rjk's suggestion is the right way to go. Bear in mind, though, that this query (with or without a union) can't use indexes, so it's not going to scale well. You might want to check out MySQL's fulltext indexing, which will scale better, allow more sophisticated queries, and even help with result ranking.

Answer 5

You could use a case to sort by:

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

Answer 6

How about something like this...

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 7

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 the derived CASE statment, it may not have good performance.