Database

Order By with Group By in MySQL

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].

[gads]

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.

[gads]

[cc lang=”sql”]
SELECT ID, post_type FROM wp_posts GROUP BY post_type ORDER BY ID DESC;
[/cc]

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 [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]

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 [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.

Shares:
  • anonyme
    anonyme
    November 23, 2012 at 12:07 am

    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.

    Reply
  • anonyme
    anonyme
    December 20, 2019 at 2:53 pm

    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.

    Reply
  • anonymous
    anonymous
    November 23, 2012 at 3:14 pm

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

    Reply
  • Ashish Jain
    Ashish Jain
    November 23, 2012 at 7:55 pm

    Why the first query not giving expected result?

    Reply
    • Avinash
      November 29, 2012 at 8:51 am

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

      Reply
  • anonymous
    anonymous
    December 20, 2019 at 2:53 pm

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

    Reply
  • Ashish Jain
    Ashish Jain
    December 20, 2019 at 2:53 pm

    Why the first query not giving expected result?

    Reply
  • Ali Emre
    December 5, 2012 at 8:18 pm

    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

    Reply
  • Ali Emre
    December 20, 2019 at 2:53 pm

    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

    Reply
  • Z Thakker
    Z Thakker
    March 22, 2018 at 12:40 pm

    Well that’s not worked for me

    Reply

Leave a Reply

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