I would like to get a single record for each distinct Name from example table below with the most recent Date.
I would hope for the result for the query to give:
Mark Checked_in 04-AUG-15 Blue
Dan Checked_in 08-JUL-15 Yellow
Example table:
Name Action Date Color
Mark Checked_in 04-AUG-15 Blue
Mark Checked_in 02-AUG-15 Black
Dan Checked_in 02-JUL-15 Orange
Dan Checked_in 08-JUL-15 Yellow
WITH WithRN as (SELECT Name
, Action
, "Date"
, Color
, row_number() over (partition by Name order by "Date" desc) as RN
FROM tableName)
SELECT Name, Action, "Data", Color
FROM WithRN
WHERE RN = 1
Analytic functions will generally perform better then join to self with aggregate.
Use an inline view to generate a subset which contains the unique value and the max date value.. Here I'm assuming Name, and then join it back to the base set to yield all the desired columns and only the record in question max(date) in this case.
SELECT A.Name, A.Action, A.Date, A.Color
FROM tableName A
INNER JOIN (SELECT name, max(date) MDate from tableName group by name) B
on A.Name = B.Name and A. Date = B.MDate
This assumes date is unique for each name.
