max qty with date

[Cap2010]

hi all,

I am really do not know how to get this. Tried all types of queries.
The requirement is as below

itemid maxqty Mdate (date is when the max qty went out)
001 5 5/1/2004
002 2 5/10/2004

The actual table has all transaction data as below.
itemid qty date
001 5 5/1/2004
001 3 5/2/2004
001 3 5/3/2004
001 2 5/4/2004
002 0 5/1/2004
.....
002 2 5/10/2004

one query is
select itemid,max(qty) from mTran

with the above require which date it occurred.

Please help

Cap2010

 

[amorous]

Try this:

SELECT t1.itemid,t1.qty AS maxqty,t1.yourdate As MDate
FROM [MyTable] t1
WHERE( (t1.qty In (select top 1 t2.qty from [MyTable] t2 where t2.itemid = t1.itemid ORDER BY t2.qty Desc) )
AND
((t1.yourdate In (select top 1 t2.yourdate from [MyTable] t2 where t2.itemid = t1.itemid ORDER BY t2.yourdate Desc) )
)
);

date is a reserved name...so avoid using it as the field name...

-VJ

 

[PHV]

Have you tried something like this ?
SELECT A.itemid, A.qty As maxqty, A.date As Mdate
FROM mTran A INNER JOIN
(select itemid,max(qty) as maxq from mTran group by itemid) B
ON (A.itemid=B.itemid) AND (A.qty=B.maxq)
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Cap2010]

PHV,

Thanks, it is done.
Made two query.
Query 1 Has all the criteria with dates.
Query 2 used your query it worked.

amorous : what is t2.yourdate ?

Thanks to all.

Cap2010