Simple(?) query issue

[flaviooooo]

Hi,

I've had to do this several times, and I always end up using a workaround:

I have a table which contains:

ORDERNR, MATERIAL, DATE

Now I would like to create a query that gives me for each MATERIAL the ORDERNR with the highest DATE

For example:

ORDERNR MATERIAL DATE
1 A 22/1/2007
2 A 15/1/2007
3 B 11/1/2007
4 C 20/1/2007
5 B 18/1/2007

This would give me:
A -> Order 1
B -> Order 5
C -> Order 4

I always end up creating an extra query showing the highest date per material, then linking it to the order with that date.

Is there a way to do it in 1 clean query?

Thanks in advance

 

[r937]

select ORDERNR    
     , MATERIAL    
     , DATE
  from daTable as T
 where DATE =
       ( select max(DATE)
           from daTable
          where MATERIAL = T.MATERIAL )

r937.com | rudy.ca

 

[lespaul]

another option:

SELECT OrderNr, Material, [Date] 
FROM TableName A
INNER JOIN (SELECT Material, Max([Date]) As MaxDate FROM TableName GROUP BY Material) B ON A.Material = B.Material AND A.[Date] = B.MaxDate

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins