How to select id with max date group by category in PostgreSQL?

by

This is a perfect use-case for DISTINCT ON – a Postgres specific extension of the standard DISTINCT:

SELECT DISTINCT ON (category)
       id  -- , category, date  -- any other column (expression) from the same row
FROM   tbl
ORDER  BY category, date DESC;

Careful with descending sort order. If the column can be NULL, you may want to add NULLS LAST:

DISTINCT ON is simple and fast. Detailed explanation in this related answer:

For big tables with many rows per category consider an alternative approach:

More Related Contents:

  1. Select first row in each GROUP BY group?
  2. Optimize GROUP BY query to retrieve latest row per user
  3. SQL query to get most recent row for each instance of a given key
  4. Optimize groupwise maximum query
  5. Get values from first and last row per group
  6. Get most common value for each value of another column in SQL
  7. PostgreSQL: top n entries per item in same table
  8. Conditional lead/lag function PostgreSQL?
  9. Get top 1 row of each group
  10. Select rows which are not present in other table
  11. Computed / calculated / virtual / derived columns in PostgreSQL
  12. how do I query sql for a latest record date for each user
  13. Check if value exists in Postgres array
  14. What is the difference between single quotes and double quotes in PostgreSQL?
  15. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  16. Fastest check if row exists in PostgreSQL
  17. Select a Column in SQL not in Group By
  18. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  19. Slow simple update query on PostgreSQL database with 3 million rows
  20. How do I alter the position of a column in a PostgreSQL database table?
  21. Discard millisecond part from timestamp
  22. Return setof record (virtual table) from function
  23. SQL column reference “id” is ambiguous
  24. How to convert PostgreSQL 9.4’s jsonb type to float
  25. postgres – where in (list) – column does not exist
  26. incomplete information from query on pg_views [duplicate]
  27. PostgreSQL: Give all permissions to a user on a PostgreSQL database
  28. Referencing current row in FILTER clause of window function
  29. Insert data and set foreign keys with Postgres
  30. How to use a ring data structure in window functions

Leave a Comment