CODEWITHSUNDEEP
mysqlsql
Enio Carvalho
Enio Carvalho

Reputation: 153

Get a row, then others order by id

How can I get a specific row (id=x) and then the reminders? Something like:

SELECT * FROM table ORDER BY id=5 FIRST THAN id DESC

I tried to use UNION ALL, like:

(SELECT * FROM table WHERE id=5) 
 UNION ALL 
(SELECT * FROM table WHERE id!=5 ORDER BY id DESC)

but the result is unexpected since the second SELECT doesn't return the registers ordered by id (desc). In addition, in this way is neccessary to write much more.

Upvotes: 2

Views: 346

Answers (3)

kba
kba

Reputation: 19466

SELECT * FROM table ORDER BY id = 5 DESC, id ASC

This will give you something like: 5, 1, 2, 3, 4, 6, 7, ...

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269783

You can put more than one clause on the order by line:

select *
from t
order by (case when id = 5 then 1 else 0 end) desc, id desc

Also, tables and result sets in SQL are unordered. The one exception is the use of order by for a result set. I wouldn't expect the union all method to work.

Upvotes: 2

paul
paul

Reputation: 22001

This will give you '5's first, then 'not 5's

SELECT * 
FROM table 
ORDER BY case when id=5 then 0 else 1 end ASC

Upvotes: 0

Related Questions