How to use use MYSQL Group By and Order By together

Question

I have to fetch data from the table grouped by one column and ordered by another column. Normally when you perform GROUP BY on a table it will retrieve the 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 that we need to fetch the data from the wp_posts table grouped by post_type fields and descending ordered by the ID. At the very first try I have run a 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.

Id  | post_type................................................................................................
65  | attachment........................................................................................
2   | nav_menu_item.......................................................................................
187 | page ........................................................................................................
1897| post.......................................................................................................

I also tried this after a few Googling which worked for some but didn't work for me

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

The result I wanted was this

Id  | post_type.................................................................................................
1900| attachment..................................................................................
1672| nav_menu_item.................................................................................
1668| page...............................................................................................
1899| post..................................................................................................

I think the problem is the temporal table reverting the order by sorting when using GROUP BY.

Possible duplicate of [MySQL "Group By" and "Order By"](https://stackoverflow.com/questions/1066453/mysql-group-by-and-order-by) — Jayesh Dhandha, May 26 '18 at 10:33
Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Paul Spiegel, May 26 '18 at 15:23
You are trying to use an "undocumented feature", which might work in some versions but not in other. There are other solutions which are relaibale and have a better performance. — Paul Spiegel, May 26 '18 at 15:26
Found the answer here https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column — Chigozie Madubuko, May 27 '18 at 08:42

Lukasz Szozda · Accepted Answer · 2018-05-26T17:43:36.080

1

You should use:

SELECT *
FROM messages m1
WHERE message_id = (SELECT message_id
                     FROM messages m2
                     WHERE m1.user_id = m2.user_id
                     ORDER BY created_at DESC
                     LIMIT 1);

Or if you have MySQL 8.0 simple ROW_NUMBER:

SELECT *
FROM (SELECT *
            ,ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC) rn
      FROM messages) sub
WHERE sub.rn = 1

edited May 26 '18 at 17:43

answered May 26 '18 at 10:33

Lukasz Szozda

162,964
23
234
275

With a `LIMIT 1` subquery you can use `=` instead of `IN`. Actually MySQL doesn't even support `IN` conditions with `LIMIT` in the subquery. Otherwise it would be too easy to solve the "Top N per group" problem :-) – Paul Spiegel May 26 '18 at 16:07

How to use use MYSQL Group By and Order By together

1 Answers1