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. We will see how to use Order By with Group by in MySQL.
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;
I thought it is correct but it is not. Check below image for the data which I have received using this query.
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
So here is the output I have achieved which is correct. ;)
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.
Avinash Zala is leading various projects which deals with the various technology involved with the web. A combination of perfect technical and management skills. Avinash would like to chat with you and convert your imagination into the working system. You can get in touch with him on Facebook and Twitter.
View all posts by: Avinash