SQL Help on retreiving Single record 1

[AlStl]

Guys,

Help needed in developing SQL. Following is the data set:

Name project# Event# Address

XXXX 321 EVT1 233 Le
XXXX 321 EVT2 244 Cali
YYYY 3dcc EVT1 555 Chicago
XXXX 321 EVT3 NY
PPPP 7777P EVT1 CO
LLLLL 8888X EVT1 LA
LLLLL 8888X EVT2 PA
CCCC 9999X EVT1 MI

Primary is key made up of: Name, Project#, and Event#

I would like to retrieve a single record (per primary key) based on highest Event# instance. For ex:

Oracle query executed against above records set should return (order by name):

Name project# Event# Address

CCCC 9999X EVT1 MI
LLLLL 8888X EVT2 PA
PPPP 7777P EVT1 CO
XXXX 321 EVT3 NY
YYYY 3dcc EVT1 555


Thanks,

Al

 

[carp]

Try something like

SELECT * 
  FROM my_table
 WHERE (Name,project,Event) IN (SELECT name,project,max(event)
                                  FROM my_table
                                 GROUP BY name,project)
 ORDER BY name;

 

[AlStl]

Carp,

I see your logic and it makes sense. I will give it shot and let you know.


I appreciate it.

Thx.

Al