ifuwannaride ifuwannaride - 6 months ago 8
SQL Question

Selecting compared pairs from table

I don't really know how to describe it. I have a table:

ID | Name | Date
-------------------------
1 | Mike | 01.01.2016
1 | Michael | 02.03.2016
2 | Samuel | 23.12.2015
2 | Sam | 05.03.2015
3 | Tony | 02.04.2012


I want to select pairs of IDs and Names with latest dates in each pair. The result here should be:

ID | Name | Date
-------------------------
1 | Michael | 02.03.2016
2 | Samuel | 23.12.2015
3 | Tony | 02.04.2012


How do I achieve this?

Oracle Database 11g

MT0 MT0
Answer

You can do it using the ROW_NUMBER() analytic function:

SELECT id, name, "date"
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER ( PARTITION BY id ORDER BY "date" DESC ) rn
  FROM   table_name t
)
WHERE rn = 1

This requires only a single table scan (it does not have a self-join or correlated sub-query - i.e. IN (...) or EXISTS(...)).

Comments