How to put row on top in mysql query.

Question

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

Is there way i can pull this record 43 first and then everything else ASC order by name?

Trick is to do it in one query.

see this http://stackoverflow.com/questions/16568/how-to-select-the-nth-row-in-a-sql-database-table — Muhammad Zeeshan, Jul 29 '11 at 13:01

sqwk · Answer 1 · 2011-07-29T13:26:26.690

30

No UNIONs or CASEs needed:

ORDER BY id = 43 DESC, name ASC

edited Jul 29 '11 at 13:26

answered Jul 29 '11 at 13:02

sqwk

super, this was exactly what i was looking for and i can confirm it works! – Tim Aug 14 '12 at 14:03

Narnian · Answer 2 · 2011-07-29T13:06:02.710

6

Use this:

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

edited Jul 29 '11 at 13:06

answered Jul 29 '11 at 12:58

Narnian

score 0 · Answer 3 · answered Jul 29 '11 at 12:59

0

Use a union to create a query that selects the first record then appends the set of records that should appear beneath.

E.g:

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

answered Jul 29 '11 at 12:59

mdm

Union does not guarantee an order without an order by. – Jacob Jul 29 '11 at 13:03

score 0 · Answer 4 · answered Jul 29 '11 at 13:03

0

This query should add a column called priority, which has the value 1 on the record with id 43 and 0 on all others. Then you sort by priority first.

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

answered Jul 29 '11 at 13:03

sled

4 Answers4