Max with group by / distinct

Databases
1

I need to retrieve the msot recent instance of a request. A table might ocntain something like:


FILE_NUMBER      FEE_AMOUNT        FEE_DATE
000001           150                5/6/11
000001            250                 7/711

I need to return all 3 fields and only the row with the fee of 250. Im having trouble using max and group by with more than 2 columns.

2 
SELECT t.file_number
     , t.fee_amount
     , t.fee_date
  FROM ( SELECT file_number
              , MAX(fee_date) AS last_date
           FROM daTable
         GROUP
             BY file_number ) AS m
INNER
  JOIN daTable AS t
    ON t.file_number = m.file_number
   AND t.fee_date = m.last_date
3

Oh wow. Im not sure how to go about this. My problem is already within a ‘with statement’. I’ll have a look and see how I can adapt it. Thank you!

4

a little reminder for your next problem – please post all relevant information, so as not to waste people’s time

thank you

:slight_smile: