CODEWITHSUNDEEP
phpmysql
Jordan Lipana
Jordan Lipana

Reputation: 447

How to prioritize data based on a given value

Hi is there a way I can prioritize the order by of an sql result?

Example I have a $_POST['city_id'] request that has a value of 2, 5 and my table I had a rows of city_id of 1,2,3,4,5

And when I use the query below example:

SELECT * FROM table_name WHERE status = 0 ORDER BY city_id ASC

I can get a result of 2,5,1,3,4 in an order by of a city_id.

Upvotes: 0

Views: 65

Answers (2)

smwhr
smwhr

Reputation: 683

You can use `ORDER BY FIELD``

SELECT * FROM table_name WHERE status = 0 ORDER BY FIELD(city_id, 5,2) DESC, city_id ASC

Note that you have to use reverse order in the FIELD function if order of the selected items is important.

Upvotes: 0

Nick
Nick

Reputation: 147206

You can order by whether the value was in the array (using an IN expression) and then by city_id:

SELECT * 
FROM table_name 
WHERE status = 0 
ORDER BY city_id IN (2, 5) DESC, city_id ASC

Demo on dbfiddle

Upvotes: 3

Related Questions