SQL语句 GROUP BY 之后 ORDER BY 排序失效要怎么写？

Question

SELECT * FROM t_download WHERE user_id = 1 ORDER BY created_time DESC

SELECT *
FROM (
    SELECT * FROM t_download WHERE user_id = 1 ORDER BY created_time DESC
) AS subquery GROUP BY item_id;

直接这样也不行
vf

SELECT * FROM t_download WHERE user_id = 1 GROUP BY item_id ORDER BY created_time DESC

想要的结果就是图一那个排序再 GROUP BY item_id 之后应该是 1330，3995 ... 要怎么做

Answer 1

通义灵码找到答案，需要这么复杂吗？

WITH ranked_records AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY created_time DESC) AS rn
    FROM t_download
    WHERE user_id = 1
)
SELECT *
FROM ranked_records
WHERE rn = 1
ORDER BY created_time DESC;