Select latest row for each group from oracle

by

You can use analytic functions

SELECT *
  FROM (SELECT c.*,
               rank() over (partition by user_id order by ts desc) rnk
          FROM comments c)
 WHERE rnk = 1

Depending on how you want to handle ties (if there can be two rows with the same user_id and ts), you may want to use the row_number or dense_rank function rather than rank. rank would allow multiple rows to be first if there was a tie. row_number would arbitrarily return one row if there was a tie. dense_rank would behave like rank for the rows that tied for first but would consider the next row to be second rather than third assuming two rows tie for first.

More Related Contents:

  1. GROUP BY with MAX(DATE) [duplicate]
  2. Return row with the max value of one column per group [duplicate]
  3. Get top results for each group (in Oracle)
  4. Fetch the row which has the Max value for a column
  5. Oracle SQL query: Retrieve latest values per group based on time [duplicate]
  6. Fetch the rows which have the Max value for a column for each distinct value of another column
  7. SQL: getting the max value of one column and the corresponding other columns [duplicate]
  8. Search All Fields In All Tables For A Specific Value (Oracle)
  9. Oracle: how to UPSERT (update or insert into a table?)
  10. Paging with Oracle
  11. ORA-12560: TNS:protocol adaptor error
  12. How do you create a temporary table in an Oracle database?
  13. What exactly do quotation marks around the table name do?
  14. Calculating difference between two timestamps in Oracle in milliseconds
  15. How can I get column names from a table in Oracle?
  16. How to store only time; not date and time?
  17. How does GROUP BY work?
  18. How can I find which tables reference a given table in Oracle SQL Developer?
  19. What’s the difference between RANK() and DENSE_RANK() functions in oracle?
  20. mySQL Returning the top 5 of each category
  21. Oracle equivalent of Postgres’ DISTINCT ON?
  22. How to get the last row of an Oracle table
  23. Select without a FROM clause in Oracle
  24. What is the difference between ‘YYYY’ and ‘RRRR’ in Oracle SQL
  25. Oracle – How to create a materialized view with FAST REFRESH and JOINS
  26. Oracle- Split string comma delimited (string contains spaces and consecutive commas)
  27. The network adapter could not establish the connection – Oracle 11g
  28. Oracle: SQL query to find all the triggers belonging to the tables?
  29. Oracle , != , ^= operators
  30. Product() aggregate function

Leave a Comment