Help Forming SQL Query

[babrandt]

Given that I have the generic fields Person, Number, and Date

I would like to create a SQL query that returns for each unique person, the number associated with the most recent date.

E.g.,
PERSON NUMBER DATE
1 3 1/1/2004
1 5 1/1/2003
1 7 6/1/2004
2 5 1/1/2000
1 10 1/1/2001
2 54 1/1/2002
2 32 1/1/1999

This query would return
PERSON NUMBER
1 7
2 54

Any help would be most appreciated! Thanks!

 

[BML]

Try the following:

SELECT PERSON, NUMBER, MAX(DATE)
FROM TABLE
WHERE DATE <= SYSDATE
GROUP BY PERSON, NUMBER

If DATE is always less than the current date, delete that row.

Hope this helps!

 

[rtmpxr]

Suppose your table is named SALES. Then the query is:

select s1.person, s1.number from sales s1 where s1.date = (select max(s2.date) from sales s2 where s2.person=s1.person) order by s1.person

Verified and it works

HTH