CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
David Hayward
David Hayward

Reputation: 189

SQL Server select max date per ID

I am trying to select max date record for each service_user_id for each finance_charge_id and the amount that is linked the highest date

select distinct 
    s.Finance_Charge_ID, MAX(s.start_date), s.Amount  
from
    Service_User_Finance_Charges s
where 
    s.Service_User_ID = '156'
group by 
    s.Finance_Charge_ID, s.Amount

The issue is that I receive multiple entries where the amount is different. I only want to receive the amount on the latest date for each finance_charge_id

At the moment I receive the below which is incorrect (the third line should not appear as the 1st line has a higher date)

Finance_Charge_ID   (No column name)    Amount
2                      2014-10-19       1.00
3                      2014-10-16       500.00
2                      2014-10-01       1000.00

Upvotes: 3

Views: 14460

Answers (2)

user330315
user330315

Reputation:

This can be done using a window function which removes the need for a self join on the grouped data:

select Finance_Charge_ID,
       start_date,
       amount
from (
  select s.Finance_Charge_ID, 
         s.start_date,
         max(s.start_date) over (partition by s.Finance_Charge_ID) as max_date,
         s.Amount  
  from Service_User_Finance_Charges s
  where s.Service_User_ID = 156
) t
where start_date = max_date;

As the window function does not require you to use group by you can add any additional column you need in the output.

Upvotes: 1

DavidG
DavidG

Reputation: 119186

Remove the Amount column from the group by to get the correct rows. You can then join that query onto the table again to get all the data you need. Here is an example using a CTE to get the max dates:

WITH MaxDates_CTE (Finance_Charge_ID, MaxDate) AS
(
    select  s.Finance_Charge_ID, 
            MAX(s.start_date) MaxDate
    from Service_User_Finance_Charges s
    where  s.Service_User_ID = '156'
    group by s.Finance_Charge_ID
)

SELECT *
FROM Service_User_Finance_Charges
JOIN MaxDates_CTE 
    ON MaxDates_CTE.Finance_Charge_ID = Service_User_Finance_Charges.Finance_Charge_ID
    AND MaxDates_CTE.MaxDate = Service_User_Finance_Charges.start_date

Upvotes: 5

Related Questions