SELECT * FROM table WHERE product like '%abc%' OR product like '%cde%'
Is it possible to display all products with
both 'abc' and 'cde' first
and then products with
either 'abc' or 'cde'
-
1Case expression in the ORDER BY.jarlh– jarlh2016-11-23 11:23:29 +00:00Commented Nov 23, 2016 at 11:23
-
SELECT * FROM table WHERE (product like '%abc%' OR product like '%cde%') order by column1,column2 ascRafael Shkembi– Rafael Shkembi2016-11-23 11:23:47 +00:00Commented Nov 23, 2016 at 11:23
-
@RafaelShkembi, you missed the order part.jarlh– jarlh2016-11-23 11:24:24 +00:00Commented Nov 23, 2016 at 11:24
-
@jarlh i'am sorry but i don't get it.. could you please tell me where i'am wrong?Rafael Shkembi– Rafael Shkembi2016-11-23 11:26:08 +00:00Commented Nov 23, 2016 at 11:26
-
i know the simple order function. But i need to display columns with abc and cde first.jomin v george– jomin v george2016-11-23 11:28:17 +00:00Commented Nov 23, 2016 at 11:28
|
Show 10 more comments
3 Answers
ORDER BY a case expression that returns 1 if both abd and cde, or else 2.
SELECT *
FROM table
WHERE product like '%abc%' OR product like '%cde%'
ORDER BY case when product like '%abc%' AND product like '%cde%' then 1
else 2
end
3 Comments
jomin v george
can you please post it like php mysql way. So that i can test it
sagi
Maybe I misunderstood the question, but I thought that there could be different records for each product , 1 that answers the first condition and one that answer the second .
jarlh
@jdoe, I don't know php, or MySQL (very well). This is ANSI SQL, and is expected to run on all dbms products, including MySQL in this case.
Try using a correlated query in the ORDER BY clause :
SELECT t.*
FROM table t
WHERE product like '%abc%' OR product like '%cde%'
ORDER BY (SELECT COUNT(DISTINCT product) FROM table s
WHERE t.<Relation> = s.<Relation>
AND s.product like '%abc%' OR s.product like '%cde%') DESC
Replace <Relation> with the tables key .
Comments
For refined ordering:
SELECT * FROM table
WHERE product like '%abc%' OR product like '%cde%'
ORDER BY
CASE
/* First show products with 'abc' AND 'cde'... */
WHEN product LIKE '%abc%' AND product LIKE '%cde%' THEN 1
/* ...2nd products with with 'abc' */
WHEN product LIKE '%abc%' THEN 2
/* ...and finally products with with 'cde' */
WHEN product LIKE '%cde%' THEN 3
ELSE 4 /* not actually necessary in this case */
END
