CODEWITHSUNDEEP
mysql
zaw
zaw

Reputation: 684

mysql order by case when

I am trying to get specific sorting with mysql. here is the table I have

id      fb_id   ...   date_added
1       1111111111    2013-04-15 14:15:00
2       1111111111    2013-04-15 14:16:00
3       1111111111    2013-04-15 14:17:00
4       2222222222    2013-04-15 14:18:00
5       3333333333    2013-04-15 14:19:00
6       4444444444    2013-04-15 14:20:00
7       2222222222    2013-04-15 14:21:00
8       1111111111    2013-04-15 14:22:00

What I am expecting is to sort by fb_id 111111111 and 2222222222 first then the rest in latest date order like this...

id      fb_id   ...   date_added
1       1111111111    2013-04-15 14:22:00
2       2222222222    2013-04-15 14:21:00
3       2222222222    2013-04-15 14:18:00
4       1111111111    2013-04-15 14:17:00
5       1111111111    2013-04-15 14:16:00
6       1111111111    2013-04-15 14:15:00
7       4444444444    2013-04-15 14:20:00
8       3333333333    2013-04-15 14:19:00

SQL I'm trying:

SELECT `id`, `fb_id`, `date_added` from MyTable
ORDER BY case when fb_id in (1111111111,2222222222) then -1 else date_added end, date_added desc

It sorted by fb_id first but the rest data are not sorted by the 'date_added desc'? Please help..

Upvotes: 0

Views: 8182

Answers (2)

paxdiablo
paxdiablo

Reputation: 881523

Solutions which use row-based functions should be looked at circumspectly, they sometimes cause problems when you try to scale up.

If your table remains relatively small, that shouldn't be a problem but, if you're concerned, you can try something like:

select 1 as seq, id, fb_id, date_added
    where fb_id in (1111111111,2222222222)
union all select 2 as seq, id, fb_id, date_added
    where fb_id not in (1111111111,2222222222)
order by seq, fb_id, date_added desc

This results in two queries to the DBMS but this can often be faster than having to perform extra processing on each row of the result set before ordering.

As with all solutions, you should measure the impact rather than guessing, and do it with representative data.

Upvotes: 2

lc.
lc.

Reputation: 116498

You are sorting by date_added in ascending order first due to your ELSE clause. Try:

ORDER BY case when fb_id in (1111111111,2222222222) then 0 else 1 end, 
    date_added desc

Upvotes: 6

Related Questions