2

I want SQL to show / order the results for the column name first then show results for the description column last.

Current SQL query:

SELECT * FROM products WHERE (name LIKE '%$search_query%' OR description LIKE '%$search_query%')

I tried adding order by name, description [ASC|DESC] on the end but that didn't work.

It's for optimizing the search results. If a certain word is found in description it should go last if a certain word is also found in the name column.

Niels
  • 1,005
  • 1
  • 8
  • 18
  • So, word only in `name` is first, word only in `description` is second, word in `name` and `description` is last? – nice_dev Mar 05 '19 at 16:12

3 Answers3

3

You can use a CASE statement in an ORDER BY to prioritize name. In the example below all results where name is matched will come first because the CASE statement will evaluate to 1 whereas all other results will evaluate to 2.

I'm not sure by your problem description what exactly you want the behavior to be, but you can certainly use this technique to create more refined cases to prioritize your results.

SELECT * 
FROM products 
WHERE (name LIKE '%$search_query%' OR description LIKE '%$search_query%') 
ORDER BY CASE WHEN name LIKE '%$search_query%' THEN 1 ELSE 2 END
Daniel Gimenez
  • 18,530
  • 3
  • 50
  • 70
0

If you want the names first, the simplest order by is:

order by (name like '%$search_query%') desc

MySQL treats booleans as numbers in a numeric context, with "1" for true and "0" for false.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

While this is undocumented, when results sets combined by a UNION ALL and not sorted afterwards, they stay in the order returned, as UNION ALL just adds new results to the bottom of the result set. This should work for you:

SELECT * FROM products
WHERE name LIKE '%$search_query%' 
UNION ALL
SELECT * FROM products
WHERE (description LIKE '%$search_query%' AND name NOT LIKE '%$search_query%')
Andrew
  • 1,544
  • 1
  • 18
  • 36