PostgreSQL – fetch the rows which have the Max value for a column in each GROUP BY group

by

I would propose a clean version based on DISTINCT ON (see docs):

SELECT DISTINCT ON (usr_id)
    time_stamp,
    lives_remaining,
    usr_id,
    trans_id
FROM lives
ORDER BY usr_id, time_stamp DESC, trans_id DESC;

More Related Contents:

  1. Optimize groupwise maximum query
  2. SELECT DISTINCT is slower than expected on my table in PostgreSQL
  3. Select first row in each GROUP BY group?
  4. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  5. How to use RETURNING with ON CONFLICT in PostgreSQL?
  6. PostgreSQL DISTINCT ON with different ORDER BY
  7. Split comma separated column data into additional columns
  8. How to delete duplicate entries?
  9. Escaping keyword-like column names in Postgres
  10. Transpose latest rows per user to columns
  11. Delete duplicate rows (don’t delete all duplicate)
  12. GROUP BY + CASE statement
  13. subquery in FROM must have an alias
  14. Compute percents from SUM() in the same SELECT sql query
  15. SQL Between clause with strings columns
  16. Insert an image in postgresql database
  17. Perform this hours of operation query in PostgreSQL
  18. Getting date list in a range in PostgreSQL
  19. Postgres Dynamic Query Function
  20. How do I delete a fixed number of rows with sorting in PostgreSQL?
  21. Does the JDBC spec prevent ‘?’ from being used as an operator (outside of quotes)?
  22. How to use an ALIAS in a PostgreSQL ORDER BY clause?
  23. Postgresql Column Not Found, But Shows in Describe
  24. PostgreSQL does not accept column alias in WHERE clause
  25. Why can’t I use column aliases in the next SELECT expression?
  26. Postgres window function and group by exception
  27. SQL LIKE condition to check for integer?
  28. Counting the number of occurrences of a substring within a string in PostgreSQL
  29. Temporal database design, with a twist (live vs draft rows)
  30. How to bulk insert only new rows in PostreSQL

Leave a Comment