Home  »  Tips & Tricks   »   Order By with Group By in MySQL

Order By with Group By in MySQL

By Avinash

November 09, 2012Tips & Tricks6 Comments

Its a post after so long time, actually very busy schedule. But today I got something to learn to I have managed spare some time for the same and consider sharing with all my readers.

The problem was something like this. I have to fetch data from the table grouped by one column and ordered by another column.

Normally when you perform GROUP BY on table it will retrieve first row in that group. But my task was to get the last row in that GROUP.

I am taking an example of the wordpress’ post table’s example. Consider we need to fetch the data from the wp_posts table grouped by post_type fields and descending ordered by the ID.

At very first try I have run query like below.

SELECT ID, post_type FROM wp_posts GROUP BY post_type ORDER BY ID DESC;
SELECT ID, post_type FROM wp_posts GROUP BY post_type ORDER BY ID DESC;

I thought it is correct but it is not. Check below image for the data which I have received using this query.

Order by with Group By MySQL

Then after I have done quick googling and found the correct way to perform ORDER BY along with GROUP BY. So below is what I have accomplished with.

SELECT subtable.ID, subtable.post_type FROM (SELECT * FROM wp_posts ORDER BY ID DESC) as subtable GROUP BY post_type
SELECT subtable.ID, subtable.post_type FROM
(SELECT * FROM wp_posts ORDER BY ID DESC) AS subtable
GROUP BY post_type

So here is the output I have achieved which is correct. ;)

Order by with Group By MySQL

Here you think its a two queries but its not. Here result of the inner query is stored under some temp table which are shorted as per our need. And we are performing the GROUP BY on that sorted data so obviously we will get the last row and Grouped as well.

Hope you find this helpful as I have. Consider sharing your views and also any other approach for the same.

Share This Article

Related Posts

Tags:

Written by Avinash

Avinash Zala currently working at a leading web development company as a Sr. PHP Web Developer. He is the founder of the Xpert Developer. He has completed B.E. in Information Technology. Born and brought up in Ahmedabad. He loves to code and coding is all about passion for him. Follow him on twitter at Avinash Zala & Xpert Developer.

  • anonyme

    I have been looking for this for a while but did’nt have enough time to find a solution.

    I have just been here by random

    Thank you for sharing.

  • anonymous

    Why didn’t you use:
    SELECT max(ID), post_type FROM wp_posts GROUP BY post_type;

    • http://www.xpertdeveloper.com Avinash

      Excellent.!!!!!!!

  • Ashish Jain

    Why the first query not giving expected result?

    • http://www.xpertdeveloper.com Avinash

      You can see the different in the ID in both the results.

  • http://www.gncart.net Ali Emre

    Like Ashish Jain said, your solution did not perform as you expect on my database and I change the code like:
    SELECT suitable.id, suitable.title FROM (SELECT * FROM site_tags GROUP BY title) AS suitable ORDER BY id DESC