Getting corresponding fields of max date

[robin thomas]

I have 3 fields in my table. id,date and quantity. I want to take the value of quantity field corresponding to max of date field. Please help me.

[russell]

what dbms?

something like this will work, depending on dbms

select a.id, a.date, a.qty
from yourtable a
join (
select id, max(date) dt
from yourtable
group by id
) b
on a.id = b.id
and a.date = b.date

[robin thomas]

Sorry. I havnt given full information. Those fields are from 2 tables.date is in another table. You can join the tables using id and sid. Please help me.

[russell]

post the schema of the 2 tables. also what is dbms?

[robin thomas]

post the schema of the 2 tables. also what is dbms?

DB is sql 2005

Table A
id int,qty double

Table B

aid int, ddate datetime

[russell]

select a.id, b.dt, a.qty
from TableA a
join (
select aid, max(ddate) dt
from TableB
group by aid
) b
on a.id = b.aid