How to put a line on top in a mysql query.

Question

Hi, I have 100 records in my SQL table. I want to sort them by name ASC, but I need one record on top of all nr 43 records.

Is there a way I can transfer this entry 43 first and then everything else in order of the ASC name?

The trick is to do this in one request.

Answer 1

No UNION or CASEs needed:

ORDER BY id = 43 DESC, name ASC 

Answer 2

Use this:

 ORDER BY CASE WHEN (record is 43) THEN 0 ELSE 1 END, Name 

Answer 3

Use union to create a query that selects the first record and then adds a set of records that should appear below.

eg:

 select * from table where id = 43; union select * from table where id <> 43; 

Answer 4

This query should add a column named priority that has a value of 1 in the record with id 43 and 0 for everyone else. Then first sort by priority.

 SELECT mytable.*, IF(id = 43, 1, 0) AS priority FROM mytable ORDER BY priority DESC, name ASC