CODEWITHSUNDEEP
mysqlsqlsql-order-by
rusly
rusly

Reputation: 1522

mysql order by best match and order by field

Below is my query for search keyword and then ORDER BY FIELd

SELECT * FROM mall WHERE mall_status = '1' AND 
(mall_name LIKE '%Cap%' OR mall_name LIKE '%Square%' OR mall_name = 'Cap Square' OR 
tag LIKE 'Cap Square,%' OR tag LIKE '%,Cap Square' OR tag LIKE '%, Cap Square' OR tag LIKE '%,Cap Square,%' OR tag LIKE '%, Cap Square,%' OR tag = 'Cap Square' ) 
ORDER BY FIELD(state_id, 14, 10, 5, 4, 1, 6, 11, 3, 2, 7, 8, 15, 12, 13) ASC , mall_name LIMIT 0,30

and the result will show based on state_id order, but now how to return best matches for first row only and then follow by state_id( ORDER BY FIELD ) . Best match is something like mall_name = 'Cap Square' .

so the result is something like this :

Cap Square (state id: 10)
Central Square (state id: 14)
Berjaya Times Square (state id: 14)
Oasis Square (state id: 10)
Shaftsbury (state id: 5)
Penang Times Square (state id: 4)

Upvotes: 0

Views: 1302

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

I think you can do the ordering like this:

SELECT *
FROM mall
WHERE mall_status = '1' AND 
      (mall_name LIKE '%Cap%' OR mall_name LIKE '%Square%' OR mall_name = 'Cap Square' OR 
       tag LIKE 'Cap Square,%' OR tag LIKE '%,Cap Square' OR tag LIKE '%, Cap Square' OR
       tag LIKE '%,Cap Square,%' OR tag LIKE '%, Cap Square,%' OR tag = 'Cap Square'
      ) 
ORDER BY mall_name = 'Cap Square' desc,
         FIELD(state_id, 14, 10, 5, 4, 1, 6, 11, 3, 2, 7, 8, 15, 12, 13) ASC , mall_name
LIMIT 0, 30 ;

Upvotes: 1

Related Questions