Unable to get nested query to work

[tsyle]

InvDate Division CustomerNum
4/7/2008 30 10
1/7/2008 50 10
1/7/2008 50 10
10/8/2007 50 10
10/8/2007 50 10
7/9/2007 50 10
7/9/2007 50 10
4/11/2007 50 10
4/11/2007 50 10
4/11/2007 50 10
2/9/2007 50 10
12/7/2007 50 15
2/12/2007 50 15
1/7/2008 30 26
10/8/2007 30 26
10/8/2007 30 26
9/21/2007 30 26
7/9/2007 30 26
5/3/2007 30 26
2/19/2008 30 31
4/2/2007 30 39
2/15/2008 30 45
1/28/2008 30 53

I need to find the newest date from a list of distinct values (Division and CustomerNum)

The solution to the above would be

4/7/2008 30 10
12/7/2007 50 15
1/7/2008 30 26
2/19/2008 30 31
4/2/2007 30 39
2/15/2008 30 45
1/28/2008 30 53

The query that I was thinking of going with was

SELECT * FROM table
WHERE CustomerNum IN (SELECT DISTINCT CustomerNum, Division
FROM table)

I know this query doesn't work because you can't use IN with a table with more than 1 column... but I just can't figure this one out.

Any help will be greatly appreciated.

 

[pjm]

Hi

select division, customernum, max(invdate) 
from table
group by division, customernum

Should do it...

pjm

 

[tsyle]

thanks! that worked. I can't believe it was something simple. I was trying to make it harder than it should be.