MySQL - using GROUP BY and DESC

Question

In my SQL query I am selecting data with GROUP BY and ORDER BY clauses. The table has the same numbers across multiple rows with different times in each row. So I think I want to apply a GROUP BY clause.

However in the results return the oldest time with the number, but I need the most recent time.

SELECT * FROM TABLE GROUP BY (numbers) ORDER BY time DESC

The query appears as if it should first apply GROUP BY and then ORDER BY... but the results do not appear to work this way.

Is there any way to fix this?

[In the docs](http://dev.mysql.com/doc/refman/5.6/en/group-by-hidden-columns.html) the behaviour you get if you select columns that are not grouped by is explicitly called out as "indeterminate" in the event that they have multiple values per group. You can't rely on any particular behaviour. Do you need the whole row or just `numbers, max(time)` — Martin Smith, Sep 05 '11 at 09:24
Can you provide some rows of data with the result you expect ? — Benjamin Crouzier, Sep 05 '11 at 09:24
Related: http://stackoverflow.com/questions/1066453/mysql-group-by-and-order-by — Mukesh Chapagain, Jan 27 '13 at 18:05

score 22 · Answer 1 · answered Sep 05 '11 at 09:40

22

SELECT * 
FROM table t
WHERE time = (
    SELECT max(time)
    FROM table
    WHERE t.numbers = numbers
)

answered Sep 05 '11 at 09:40

Karolis

9,396
29
38

When I try to run this query on a dummy table, somehow I end up getting all the rows. Removing the WHERE inside subquery works for me, however. – hmishra2250 Apr 14 '21 at 13:42

score 18 · Accepted Answer · answered Sep 05 '11 at 09:29

18

work-around is to re-write the query as:

SELECT * FROM (SELECT * FROM table ORDER BY time DESC) AS t GROUP BY numbers;

answered Sep 05 '11 at 09:29

jbrond

727
8
20

5

In theory you cannot guarantee that you will always get the same result. – Karolis Sep 05 '11 at 09:44
1

@Karolis agreed, your answer seems to be the more correct one – feeela Sep 05 '11 at 09:59
4

This uses a MySQL extension to `group by` that is explicitly documented not work reliably: http://dev.mysql.com/doc/refman/5.7/en/group-by-extensions.html. – Gordon Linoff Sep 07 '14 at 03:13
1

@GordonLinoff I guess this is why it didn't work for me, thanks for mentioning that – Accountant م Sep 09 '16 at 20:37
@GordonLinoff Link is broken. Any idea for what I should be searching for? Thanks – Houman May 28 '18 at 15:17
@Houman . . . How strange. You can use the 8.0 version: https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html. – Gordon Linoff May 28 '18 at 15:26
@GordonLinoff Thank you for the link, Strange this answer doesn't seem to work on MariaDB 10.0.34. I will keep searching... – Houman May 28 '18 at 16:11
@Houman . . . This answer is wrong and should not have been accepted. Use the answer by Karolis. – Gordon Linoff May 28 '18 at 16:16
@GordonLinoff I found it why it stopped working. the version 5.7.5 of MySQL made the ONLY_FULL_GROUP_BY switch enabled by default (hence, non-deterministic GROUP BY queries became disabled). Moreover, they updated the GROUP BY implementation and the solution might not work as expected anymore even with the disabled switch. :-( – Houman May 28 '18 at 16:26
@Houman it seems that non-deterministic GROUP BY gives the same results when ONLY_FULL_GROUP_BY is disabled. See this demo https://www.db-fiddle.com/f/esww483qFQXbXzJmkHZ8VT/3 (you can switch MySQL versions at the left side and result is the same including 5.7 and 8.0). – mikep Apr 02 '19 at 07:37

score 3 · Answer 3 · answered Sep 25 '18 at 08:41

3

SELECT * FROM table
    WHERE time IN (
        SELECT MAX(time)
            FROM table
            GROUP BY numbers
    )

answered Sep 25 '18 at 08:41

B. Bohdan

480
4
12

2

Code only answers are usually [frowned upon](http://meta.stackoverflow.com/q/258673/2596334). Try adding an [explination or code comments](http://meta.stackoverflow.com/q/269981/2596334). Thanks. – Scott Solmer Sep 25 '18 at 11:05
No, we can't look at group wise maximums as a set of unrelated data. For instance this query fails here: https://www.db-fiddle.com/f/eqz6uQ1rHpiVXMFou2iM4h/0 – Karolis Dec 25 '18 at 16:24

score 0 · Answer 4 · answered Jan 05 '18 at 19:50

According to the manual you can add desc to the group by list: Example:
group by item1, item2 desc, item3

with or without rollup.

I've tried this and it works in Ubuntu version 5.5.58. The reference page is: https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html

score -6 · Answer 5 · edited Feb 09 '15 at 06:42

-6

SELECT * FROM TABLE GROUP BY numbers DESC;

This will give you last record from group.

Thanks

edited Feb 09 '15 at 06:42

smottt

3,272
11
37
44

answered Feb 09 '15 at 06:31

Harshad

7

This doesn't answer the question. They want to order before grouping. – Matt McDonald Oct 22 '15 at 12:42

MySQL - using GROUP BY and DESC

5 Answers5

Linked