CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
user2296342
user2296342

Reputation: 35

Oracle SQL single distinct column

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

Upvotes: 0

Views: 37

Answers (2)

Shannon Severance
Shannon Severance

Reputation: 18410

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.

Upvotes: 2

xQbert
xQbert

Reputation: 35343

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.

Upvotes: 0

Related Questions