CODEWITHSUNDEEP
mysqlsql-order-by
sipdorus
sipdorus

Reputation: 1053

MySql Ordering by specific field value first does not work

Hello I have a news page, I want to show the user's city news at the top. For example these are the news ordered descending by time. 

+----------+-----------+-----------------------+
| CityCode | entrytime |      newsheader       |
+----------+-----------+-----------------------+
|       11 |      3800 | great opening         |
|       10 |      3700 | flood alert           |
|       12 |      3600 | new mall              |
|       13 |      3500 | pollution at the city |
|       13 |      3400 | new mayor             |
|       12 |      3300 | house fire            |
|       11 |      3200 | traffic accident      |
|       10 |      3000 | Festival at city      |
+----------+-----------+-----------------------+

I live in the city 12. I want to see CityCode=12 at the top, then other news like this. 

+----------+-----------+-----------------------+
| CityCode | entrytime |      newsheader       |
+----------+-----------+-----------------------+
|       12 |      3600 | new mall              |
|       12 |      3300 | house fire            |
|       11 |      3800 | great opening         |
|       10 |      3700 | flood alert           |
|       13 |      3500 | pollution at the city |
|       13 |      3400 | new mayor             |
|       11 |      3200 | traffic accident      |
|       10 |      3000 | Festival at city      |
+----------+-----------+-----------------------+

I tried 

Select * from news order by FIELD(CityCode,12),entrytime desc

and

Select * from news order by CityCode=12,entrytime desc

these codes did not work. I use mysql 5.5 how can i fix this? Thanks

Upvotes: 0

Views: 615

Answers (2)

Suhel Meman
Suhel Meman

Reputation: 3852

try this : add desc in your order by clause for FIELD(CityCode,12)

Select * from news order by FIELD(CityCode,12) desc,entrytime desc

Check the limitations of FIELD function for ORDER BY

Order BY using FIELD function

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Try following query:-

SELECT *
FROM NEWS
ORDER BY CASE WHEN CityCode=12 THEN 1 ELSE 2 END;

Here is the fiddle-

http://sqlfiddle.com/#!2/f5dde/1

Upvotes: 0

Related Questions