CODEWITHSUNDEEP
mysqlsql
sureyn
sureyn

Reputation: 117

Arrange data in specific order

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.

Upvotes: 6

Views: 1064

Answers (4)

John Woo
John Woo

Reputation: 263743

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

Upvotes: 1

Neeraj
Neeraj

Reputation: 9165

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

Upvotes: 7

ApplePie
ApplePie

Reputation: 8942

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

Upvotes: 1

user330315
user330315

Reputation:

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.

Upvotes: 6

Related Questions