CODEWITHSUNDEEP
mysqlsqlgroup-bysql-order-by
user1946705
user1946705

Reputation: 2888

MySQL - using GROUP BY and DESC

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?

Upvotes: 13

Views: 65065

Answers (5)

B. Bohdan
B. Bohdan

Reputation: 529

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

Upvotes: 3

Betty Mock
Betty Mock

Reputation: 1393

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

Upvotes: 0

Harshad
Harshad

Reputation: 7

SELECT * FROM TABLE GROUP BY numbers DESC;

This will give you last record from group.

Thanks

Upvotes: -6

Karolis
Karolis

Reputation: 9562

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

Upvotes: 22

jbrond
jbrond

Reputation: 727

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

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

Upvotes: 18

Related Questions