Arrange data in specific order

Question

I have a user table that contain 8 records. I want to arrange the data in descending order on the basis of field id (that is a primary key of that table) but except id 3 and 5. So eventually the result should be like

id  name
--  ----
3   peter
5   david
8   john
7   stella
6   jim
4   jack
2   nancy
1   scott

Except id 3 and 5 rest of the data should be arranged in descending order and 3 and 5 should come in ascending order.

+1 for the question as well :D – bonCodigo Jan 16 '13 at 12:44 — bonCodigo, Jan 16 '13 at 12:44

score 7 · Answer 1 · edited Jan 16 '13 at 12:32

7

SELECT * FROM user ORDER BY IF(id=3 OR id=5, id, ~id) ASC

edited Jan 16 '13 at 12:32

a_horse_with_no_name

440,273
77
685
758

answered Jan 16 '13 at 12:29

neeraj

7,235
16
55
84

@a_horse_with_no_name: execute the query SELECT IF(id=3 OR id=5, id, ~id) FROM user and you will get the answer – neeraj Jan 16 '13 at 12:33
It gives strange numbers. What are they? – a_horse_with_no_name Jan 16 '13 at 12:36
they are temporary auto generate numbers regardless of taking care of number of records in a table. – neeraj Jan 16 '13 at 12:36
1

It is very well similar to this `SELECT * FROM table1 ORDER BY IF(id=3 OR id=5, id, id-8) desc` But here you need to define the number to the max id ;) ; – bonCodigo Jan 16 '13 at 12:50
That's right. but we don't want to bother about the max id. :) – neeraj Jan 16 '13 at 12:51
This worked better for me. SELECT * FROM `user` ORDER BY case when id = 3 then 0 when id = 5 then -1 else id end ASC – sureyn Feb 01 '13 at 11:29

score 6 · Accepted Answer · answered Jan 16 '13 at 12:28

6

something like this:

order by 
   case 
     when id = 3 then 999
     when id = 5 then 998
     else id
   end desc

This assumes that you really don't have more than 8 rows. Otherwise you must change the "magic" numbers that move 3 and 5 to the top.

answered Jan 16 '13 at 12:28

a_horse_with_no_name

440,273
77
685
758

hmm.. So if that user exceeds then in that case your query will not work? – sureyn Jan 16 '13 at 12:30
@sureyn: you could simply increase the `999` to a really big value. – a_horse_with_no_name Jan 16 '13 at 12:31

score 1 · Answer 3 · answered Jan 16 '13 at 12:29

1

I think the trick here is to use an enum.

SELECT id, name FROM my_table WHERE id IN (3, 5) ORDER BY ASC
UNION
SELECT id, name FROM my_table WHERE id NOT IN(3, 5) ORDER BY DESC

answered Jan 16 '13 at 12:29

ApplePie

8,247
5
35
55

Is there any other way without using UNION? – sureyn Jan 16 '13 at 12:31
By the SQL standard you are not able to order parts of a UNION. Does MySQL allow this nevertheless? And is the order retained even though MySQL will try to remove duplicates between the two parts? – a_horse_with_no_name Jan 16 '13 at 12:31
My bad. I guess I made assumptions on the standard. – ApplePie Jan 16 '13 at 16:29

John Woo · Answer 4 · 2013-01-16T12:55:56.187

1

In MySQL, there is a function called FIELD which *returns zero if a value is not found on the list` eg,

SELECT *
FROM   tableName
ORDER BY FIELD(id, 5, 3) DESC, id DESC

edited Jan 16 '13 at 12:55

answered Jan 16 '13 at 12:50

John Woo

238,432
61
456
464

But that return the id like 3,5,1,2,4,6 and so on... Where as output should come like 3,5,8,7,6,4.... – neeraj Jan 16 '13 at 12:53

Arrange data in specific order

4 Answers4