Max & Last in one Table

[dfeteau]

I have a table, ACTIVE, that has over a million records. There are three important columns, FILENO, TRANS_DATE & CODE. I need a query that will give me a distinct FILENO with the CODE and last (or MAX) TRANS_DATE. This is what I have so far.

SELECT FILENO, Max(TRANS_DATE) AS L_SDATE, CODE AS L_SCODE
FROM dbo_ACTIVE
GROUP BY FILENO, CODE;

The problem I'm having is that I'm getting distinct rows but i'm not getting the last or max date only and my guess is because i'm also grouping by CODE which has a number of different values. How do I get the LAST or MAX TRANS_DATE with whatever CODE is associated with it for each FILENO?

[pbaldy]

Is this what you're after?

http://www.baldyweb.com/LastValue.htm

[dfeteau]

Yes it is Paul. Note to self: Stop Overthinking.

[pbaldy]

Welcome to the site by the way!