CODEWITHSUNDEEP
mysqlsqlsql-order-by
dadord
dadord

Reputation: 71

ORDER BY specific value then rotate through the rest

I have this query:

SELECT * FROM Users;

Where I get back

 id    name  ...
 1     ...
 2     ...
 3     ...
 4     ...

Now, what I want is to order the result I get back based on the id, and rotate the order based on that. For example, lets say I want to sort based on the ID=3. What I want back is:

 id    name  ...
 3     ...
 4     ...
 1     ...
 2     ...

Is this possible directly in MySQL, or do I have to do this server side?

Upvotes: 1

Views: 497

Answers (3)

Luca Rainone
Luca Rainone

Reputation: 16468

I think simply:

SELECT * FROM Users ORDER BY id<3,id

Upvotes: 3

samuil
samuil

Reputation: 5081

You can achieve this by using

ORDER BY CASE 
    WHEN id < 3 THEN id + (SELECT MAX(id) FROM users) 
    ELSE id 
END

but note, that mysql won't be able to use index for that ordering. I think that making this rotation outside of SQL server is most pragmatic solution.

Upvotes: 1

Andomar
Andomar

Reputation: 238196

You could sort on two columns, the first only being filled when id >= 3:

order by
    case 
    when id >= 3 then -id
    end desc
,   id

The first column is sorted on -id so we can sort descending, where null is at the end.

Live example at SQL Fiddle.

Upvotes: 1

Related Questions