sqlpostgresqlsql-order-bywindow-functionssql-in

PostgreSQL get results grouped by Name but ordered by modified_date desc for the first element of each group

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:

enter image description here

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:

enter image description here

Solution

  • 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.