Query Help

[ToneSta]

I have some data from a table "unitp" as below:

Code Price Date
---- ----- ----
ABC 12.04 30/11/02
ABC 12.05 31/12/02
DEF 5.06 31/05/02
DEF 8.03 30/06/02
DEF 2.45 31/07/02

i want to able to run a query that will output the record with the maximum Date showing the Code and Price, ie:

Code Price
---- -----
ABC 12.05
DEF 2.45

I have only been able to get this far, which doesn't really get what i want:

SELECT code, price, date
FROM unitp
WHERE code = 'ABC'
AND date = (SELECT MAX(date)
FROM unitp
WHERE code = 'ABC')

Would anyone know what would be the best way?

Many Many Thanks

 

[fluteplr]

SELECT code, price, date
FROM unitp
where date in (SELECT MAX(date)
FROM unitp unitp2
WHERE unitp.code = unitp2.code)

this assumes that each code can have only one record per date

 

[sunila7]

Hi,

I think this should work..

SELECT code,price,date from
unitp P
INNER JOIN
(SELECT code, Max(date) dte
FROM unitp
Group by code) TBL ON TBL.Code=P.Code and TBL.dte= p.dte

Hope it helps...


Sunil

 

[ToneSta]

Thanks guys ....fluteplr, yours did the trick. Couldn't get sunila7's working ...might be syntax or something. i'm using sql 8. hmmmm