Max Date Help

[Griggsy]

Hi

I need to create an SQL to retrieve the max date for each change in stock code. can anyone assist please? I am extremely new to SQl. I used access to get external data but it drags down 60,000 odd lines. i only have 5000 line items so with max date function working it will only have to retrieve 5,000 odd lines

stock_code stk_trans_type stk_trans_whse stk_trans_date
10016 MU WIL 9/02/2009
10016 MU WIL 7/02/2010
10016 MU WIL 5/03/2009
10016 MU WIL 29/05/2009
10016 MU WIL 26/02/2010
10016 MU WIL 21/05/2009
10016 MU WIL 21/04/2009
10016 MU WIL 20/09/2009
10016 MU WIL 20/07/2009
10016 MU WIL 20/05/2009
10016 MU WIL 18/04/2009
10016 MU WIL 18/03/2009
10016 MU WIL 16/02/2010
10016 MU WIL 12/11/2009
10016 MU WIL 11/06/2009
10016 MU WIL 1/11/2009
10016 MU WIL 1/11/2009
10016 MU WIL 1/05/2009
10017 MU WIL 9/02/2010
10017 MU WIL 8/11/2009
10017 MU WIL 8/05/2009
10017 MU WIL 8/02/2010
10017 MU WIL 7/06/2009
10017 MU WIL 7/06/2009
10017 MU WIL 7/06/2009
10017 MU WIL 7/05/2009
10017 MU WIL 7/03/2009
10017 MU WIL 7/01/2010
10017 MU WIL 6/09/2009
10017 MU WIL 5/12/2008
10017 MU WIL 5/10/2009
10017 MU WIL 5/05/2009

[samaru]

I'm not quiet sure what your problem is. Maybe what do you mean by "drags down 60,000 odd lines." Well, another way to get the max date would be:

select top 1 stk_trans_date 
from [YOUR TABLE NAME] 
order by stk_trans_date desc

Asuming stk_trans_date is of date type. Is it?

[pclfw]

samaru; Your code will only return one row. The correct code is -

SELECT DISTINCT t.stock_code, t.stk_trans_type, t.stk_trans_whse, t.tk_trans_date 
from [YOUR TABLE NAME] t
WHERE t.tk_trans_date = (SELECT MAX(tk_trans_date)
                                    FROM [YOUR TABLE NAME] s
                                    WHERE s.stock_code       = t.stock_code
                                    AND     s.stk_trans_type  = t.stk_trans_type 
                                    AND     s.stk_trans_whse = t.stk_trans_whse)