SQL Select unique records with max date for Oracle10g

Experts,

I am stumped on a SQL select query against an Oracle 10g database.
I need to select every unique combination of CUST and ITEM. Where there is more than one, I need the most recent date returned. I put an asterix on the desired results. The date is a proper system date and sorts properly in a query.

Cust      Item            Date (YY-MM-DD)
A      COOKIE        11-09-15 *
A      COOKIE        10-06-24
A      COOKIE        98-07-21
A      PIZZA            08-02-15 *
A      DONUT            10-02-21 *
B      PIZZA            11-05-03 *
B      PIZZA            08-12-31
B      PIZZA            06-06-07
B      DONUT            10-11-05 *
C      DONUT            99-02-03 *
C      PIZZA            03-04-18 *

JDCam

Asked: 2011-09-14

Advertise Here

1 Solution

LVL 79

slightwv (䄆 Netminder) Commented: 2011-09-14

try this:

select cust, item, date_col from
(
select cust, item, date_col, row_number() over(partition by cust, item order by date_col desc) myrownum from table_name
)
where myrownum=1