subquery? max? 1

[Pampers]

Hi everyone,
Looking for a query that select the following:

id date number
1 12-11-2011 3
2 12-11-2011 5

from this dataset:

id date number
1 11-11-2011 2
1 12-11-2011 3
2 11-11-2011 4
2 12-11-2011 5
...

Can't figure it out
Regards Peter.

Pampers
Keeping it simple can be complicated

 

[PWise]

how about

select id,date,number
from tablename
where date=#12-11-2011#

 

[traingamer]

Not enough information to give a good answer.

Are you looking for the maximum date for each id?
And the number that goes with it?
Is there a primary key?

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[PHV]

You wanted this ?

SELECT A.id, A.date, A.number
FROM yourTable A INNER JOIN (
SELECT id, MAX([date]) AS LastDate FROM yourTable GROUP BY id
) L ON A.id = L.id AND A.date = L.LastaDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Pampers]

Yep great PHV,
that was what I was looking for.

Also thanks to the others for their input.

Pampers
Keeping it simple can be complicated