CODEWITHSUNDEEP
mysql
user2083041
user2083041

Reputation: 513

Is custom sorting pattern possible in MySQL?

I have a table whose values are suppose

c,d,b,a

I want to sort the values except 'a'. It should always be at the bottom. The order should be

b,c,d,a.

Upvotes: 0

Views: 62

Answers (3)

jpw
jpw

Reputation: 44891

You can use a case statement to do this:

select * from table1
order by case when val = 'a' then 1 else 0 end, val

This will sort all values that are not 'a' before 'a', and then by the value.

Sample SQL Fiddle

As pointed out by Mr Zorn in a comment, and by Luke101 in another answer this statement can be simplified as: 

select * from table1
order by val = 'a', val

The former solution should work across all ANSI compliant databases, the latter works in MySQL, Postgresql and some others (but not MSSQL for instance).

Upvotes: 5

Luke101
Luke101

Reputation: 65288

select * from table1
order by val = 'a', val

Upvotes: 3

Ullas
Ullas

Reputation: 11556

First select the columns from table which NOT IN a and the sort those values and then UNION the a column.

Query

SELECT t.name FROM 
(
  SELECT * FROM tbl
  WHERE name NOT IN ('a')
  ORDER BY name
)t
UNION 
SELECT * FROM tbl
WHERE name IN ('a');

Fiddle Demo

Upvotes: 0

Related Questions