CODEWITHSUNDEEP
mysqlsql
Niddro
Niddro

Reputation: 1725

MySQL - ranking results based on parameters in the same query

In a previous question I found out how to split up my search string '01-0023' and search two columns. Now I want to also add results from a third column description. However, I want data that results from the (group, article) to show first (i.e. ranked higher/more relevant). How is this possible?

Original query: SELECT * FROM ARTICLES WHERE group= substring_index('@search_string', '-', 1) + 0 AND article= substring_index('@search_string', '-', -1) + 0

New query: SELECT * FROM ARTICLES WHERE (group= substring_index('@search_string', '-', 1) + 0 AND article= substring_index('@search_string', '-', -1) + 0) OR description LIKE '%@search_string%'

Upvotes: 1

Views: 47

Answers (2)

Olivier De Meulder
Olivier De Meulder

Reputation: 2501

SELECT * 
FROM ARTICLES 
WHERE (group= substring_index('@search_string', '-', 1) + 0 
    AND article= substring_index('@search_string', '-', -1) + 0) 
OR description LIKE '%@search_string%'
ORDER BY CASE 
    WHEN (group= substring_index('@search_string', '-', 1) + 0 
        AND article= substring_index('@search_string', '-', -1) + 0) THEN 1
    WHEN description LIKE '%@search_string%' THEN 2 END ASC

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can move the logic to the order by:

SELECT *
FROM ARTICLES
ORDER BY (`group` = substring_index('@search_string', '-', 1) + 0) DESC,
         (`article` = substring_index('@search_string', '-', -1) + 0) DESC

Upvotes: 0

Related Questions