Mysql - Using Group By And Desc

October 23, 2024 Post a Comment

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 a

Solution 1:

SELECT*FROMtable t
WHEREtime= (
    SELECTmax(time)
    FROMtableWHERE t.numbers = numbers
)

Solution 2:

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

SELECT*FROM (SELECT*FROMtableORDERBYtimeDESC) AS t GROUPBY numbers;

Solution 3:

SELECT*FROMtableWHEREtimeIN (
        SELECTMAX(time)
            FROMtableGROUPBY numbers
    )

Solution 4:

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

Solution 5:

SELECT*FROMTABLEGROUPBY numbers DESC;

This will give you last record from group.

Thanks

Lab Sql