Oracle SQL query: Retrieve latest values per group based on time [duplicate]

by

Given this data …

SQL> select * from qtys
  2  /

        ID TS                      QTY
---------- ---------------- ----------
         1 2010-01-04 11:00        152
         2 2010-01-04 11:00        210
         1 2010-01-04 10:45        132
         2 2010-01-04 10:45        318
         4 2010-01-04 10:45        122
         1 2010-01-04 10:30          1
         3 2010-01-04 10:30        214
         2 2010-01-04 10:30       5515
         4 2010-01-04 10:30        210

9 rows selected.

SQL>

… the following query gives what you want …

SQL> select x.id
  2         , x.ts as "DATE"
  3         , x.qty as "QUANTITY"
  4  from (
  5      select id
  6             , ts
  7             , rank () over (partition by id order by ts desc) as rnk
  8             , qty
  9      from qtys ) x
 10  where x.rnk = 1
 11  /

        ID DATE               QUANTITY
---------- ---------------- ----------
         1 2010-01-04 11:00        152
         2 2010-01-04 11:00        210
         3 2010-01-04 10:30        214
         4 2010-01-04 10:45        122

SQL>

With regards to your additional requirements, you can apply additional filters to the outer WHERE clause. Similarly you can join additional tables to the inline view like it was any other table.

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. Oracle SELECT TOP 10 records [duplicate]
  5. Fetch the row which has the Max value for a column
  6. Oracle SQL – How to Retrieve highest 5 values of a column [duplicate]
  7. Fetch the rows which have the Max value for a column for each distinct value of another column
  8. How to find the employee with the second highest salary?
  9. SQL: getting the max value of one column and the corresponding other columns [duplicate]
  10. Select latest row for each group from oracle
  11. Select first row in each GROUP BY group?
  12. Splitting string into multiple rows in Oracle
  13. How do I find duplicate values in a table in Oracle?
  14. How can multiple rows be concatenated into one in Oracle without creating a stored procedure? [duplicate]
  15. How to use a calculated column to calculate another column in the same view
  16. How can I create a copy of an Oracle table without copying the data?
  17. Alternatives to LIMIT and OFFSET for paging in Oracle [duplicate]
  18. How to get second largest or third largest entry from a table [duplicate]
  19. Split String by delimiter position using oracle SQL
  20. How can I get the number of days between 2 dates in Oracle 11g? [duplicate]
  21. Update with Join query in Oracle
  22. Deferrable Constraints in SQL Server
  23. How to select records with maximum values in two columns?
  24. How do I get textual contents from BLOB in Oracle SQL
  25. Autoincrement in oracle to already created table
  26. What can cause an Oracle ROWID to change?
  27. How to add ‘ON DELETE CASCADE’ in ALTER TABLE statement
  28. adding primary key to sql view
  29. How do I call an Oracle stored procedure from an Excel VBA script?
  30. Oracle Convert TIMESTAMP with Timezone to DATE

Leave a Comment