Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Select unique records with max date  for Oracle10g

Posted on 2011-09-14
3
353 Views
Last Modified: 2012-05-12
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
Comment
Question by:JDCam
  • 2
3 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 36538746
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
 

Author Closing Comment

by:JDCam
ID: 36538816
Works perfect ... Thanks !!
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36538824
Glad to help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SubQuery link 4 35
SQL Stored Proc - Performance Enhancement 15 55
Query for timesheet application 3 17
setting local variables in a cursor block 3 19
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question