Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

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  *
0
JDCam
Asked:
JDCam
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
0
 
JDCamAuthor Commented:
Works perfect ... Thanks !!
0
 
slightwv (䄆 Netminder) Commented:
Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now