You are here: Home » Lifetime Academy » Mysql Order / Sort by before Group by

Mysql Order / Sort by before Group by

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Every developer knows the horror of mysql sort/order by or select distinct using with group by. Mysql does group by before order by and you get mixed results not what you expected. This is a small solution with less performance problem:

SELECT * FROM
(
select * from `my_table` order by timestamp desc
) as my_table_tmp
group by catid
order by nid desc

In this example we get latest news in each category. We create a temp table by sorting by timestamp and group by after it. It worked for me.
Facebooktwittergoogle_plusredditpinterestlinkedinmail

4 Comments

  1. useful stuff 🙂

  2. What's wrong with

    select * from my_table group by catid order by catid, timestamp desc, nid desc;

    ?

    • Nothing wrong except your code will group by catid just before sorting by timestamp desc, so you will not get the latest timestamps for your grouped records.

  3. Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *