PostgreSQL get results grouped by Name but ordered by modified_date desc for the first element of ea-CodePudding

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:

CodePudding user response：

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.