postgresql获取结果按名称分组，但由modified_date desc订购了每个组的第一个元素

发布于 2025-02-04 08:27:40 字数 641 浏览 3 评论 0原文

假设我的查询：

select modified_date, name from table1 where name in (select name from table2 group by name) as a order by name

返回此结果：

我需要更改查询以获取结果按名称分组，但由modified_date desc对于每个组的第一个元素。

平均结果应为：

原文

Suppose that my query:

select modified_date, name from table1 where name in (select name from table2 group by name) as a order by name

return this results:

I need to change my query to get results grouped by Name but ordered by modified_date desc for the first element of each group.

Mean the result should be:

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

π浅易 2025-02-11 08:27:40

首先，您的子查询不需要子句组，因为您没有进行任何聚合。可能是：

SELECT DISTINCT name FROM table2

但是简单地工作正常：

SELECT name FROM table2

对于您的排序问题，如果modified_date的数据类型为date您可以使用max> max（）代码>窗口函数：

SELECT modified_date, name 
FROM table1 
WHERE name IN (SELECT name FROM table2) 
ORDER BY MAX(modified_date) OVER (PARTITION BY name) DESC,
         name, -- just in case 2 names have the same max modified_date
         modified_date DESC;

请参阅

First, your subquery does not need a GROUP BY clause because you are not doing any aggregation.
It could be:

SELECT DISTINCT name FROM table2

but it works fine with a simple:

SELECT name FROM table2

For your sorting problem, if modified_date's data type is DATE you can use MAX() window function:

SELECT modified_date, name 
FROM table1 
WHERE name IN (SELECT name FROM table2) 
ORDER BY MAX(modified_date) OVER (PARTITION BY name) DESC,
         name, -- just in case 2 names have the same max modified_date
         modified_date DESC;

See the demo.

回复收藏 0 原文

~没有更多了~