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

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 ... 要怎么做

阅读 808
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;
撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题
宣传栏