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 [code]GROUP BY[/code] on table it will retrieve first row in that group. But my task was to get the last row in that [code]GROUP[/code].
I am taking an example of the wordpress’ post table’s example. Consider we need to fetch the data from the [code]wp_posts[/code] table grouped by [code]post_type[/code] fields and descending ordered by the [code]ID[/code].
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.
Then after I have done quick googling and found the correct way to perform [code]ORDER BY[/code] along with [code]GROUP BY[/code]. So below is what I have accomplished with.
[cc lang=”sql”]
SELECT subtable.ID, subtable.post_type FROM
(SELECT * FROM wp_posts ORDER BY ID DESC) as subtable
GROUP BY post_type
[/cc]
So here is the output I have achieved which is correct. ;)
[gads]
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 [code]GROUP BY[/code] 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.
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.
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.
Why didn’t you use:
SELECT max(ID), post_type FROM wp_posts GROUP BY post_type;
Excellent.!!!!!!!
Why the first query not giving expected result?
You can see the different in the ID in both the results.
Why didn’t you use:
SELECT max(ID), post_type FROM wp_posts GROUP BY post_type;
Why the first query not giving expected result?
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
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
Well that’s not worked for me
Can you show what you have tried?