[MySQL]How to sort by then group by?

Question

I have table and data like this:

    id hour name
    1  0    a1
    1  1    a2
    1  2    a3
    1  3    a4
    2  1    a5
    2  1    a6 
    2  2    a7
    2  3    a8
    3  1    a9
    3  0    a10
    4  1    a11
    4  1    a12
    5  1    a13
    ……

I want to find some ids like (1,2,4 ....) and the ids' max(hour) and the unique id

result like:

id  hour  name
1   3     a4
2   3     a8
3   1     a9
4   1     a12
....

I try group by and sort by, but fail. How to write it? Thank you.

First time, when I write the question lose some info, so maybe some answer is not my want. So I update the question. Sorry , I first use stackowverflow, sorry for my mistaken.

this my wrong sql: select * from tb where id in(1,2,3,4) group by id order by hour desc.

I know how to write the right method:

this is wrong:

select id, max(hour) from tb group by tb;

this is right:

SELECT a.id, a.maxhour, b.name FROM ( SELECT id, max(hour) AS maxhour, name FROM tb where id in (1,2,3,4) GROUP BY id) a JOIN tb b ON a.id = b.id AND a.maxhour = b.hour

thank you all my friends. your answer make me know how to do it

Answer 1

You need to use aggregate function max() and group by:

SELECT id, MAX(hour) AS mhour FROM tablename
GROUP BY id
ORDER BY mhour DESC

You can use a correlated subquery

    SELECT id, hour, name FROM tablename a
        WHERE CAST(LTRIM(REPLACE(name,'a','')) AS int) IN (SELECT MAX(CAST(LTRIM(REPLACE(name,'a','')) AS int)) FROM tablename b WHERE a.id=b.id)
    ORDER BY hour DESC

Answer 2

This looks like you just want to take the max hour value for each id group. Here is one way to do this using analytic functions, assuming you are using MySQL 8+ or later:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY hour DESC) rn
    FROM yourTable
)

SELECT id, hour
FROM cte
WHERE rn = 1
ORDER BY hour DESC;

Demo

Answer 3

SELECT id,max(hour) as hour FROM table_name group by id order by id,hour desc;

Try this query.