CODEWITHSUNDEEP
sqlt-sql
TRH
TRH

Reputation: 574

Select the MAX Record with tie breaking logic

Using SQL Server 2014

I have a table with four fields. Each record contains a separate donation. It's possible for two separate donations from the same Donor to have the same amount and even the same date (say they gave the same amount twice on a given day - rare but it happens).

I'd like to return a set that has full record for each donor's maximum contribution. In cases where there are two donations of that max amount pick the most recent one. If there are two max contributions on the same day return the one with the highest IDNumber (indicating the order in which they were entered into the system)

My head exploded event thinking about this one so I put it to the group.

CREATE TABLE [dbo].[Donations1](
    [IDNumber] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [DonorIDNumber] [int] NOT NULL,
    [DTGCreated] [datetime2](7) NOT NULL, 
    [TransactionDate] [datetime2](7) NOT NULL,
    [Amount] [numeric](18, 2) NOT NULL
    )

Upvotes: 0

Views: 2948

Answers (3)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

I think it is simple with window function:

;with cte as(
select *, Row_Number() over(partition by DonorID order by Amount desc, DateCreated desc, ID desc) as rn from Donations
)
select * from cte where rn = 1

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95043

Use ROW_NUMBER to rank your records, so that the records you want get row number 1:

select *
from
(
  select 
    donations1.*, 
    row_number() over (partition by donoridnumber order by amount desc, transactiondate desc, idnumber desc) as rn
  from donations1
) donations
where rn = 1;

Upvotes: 1

Hart CO
Hart CO

Reputation: 34784

You can do this with the ROW_NUMBER() function:

;with cte AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY DonorIDNumber ORDER BY Amount DESC, TransactionDate DESC, IDNumber DESC) AS RN
              FROM Donations1
             )
SELECT *
FROM cte 
WHERE RN = 1

The ROW_NUMBER() function assigns a number to each row. PARTITION BY is optional, but used to start the numbering over for each value in a given group, ie: if you PARTITION BY DonorIDNumber then for each donor the numbering would start over at 1. ORDER BY of course is used to define how the counting should go, and is required in the ROW_NUMBER() function.

Upvotes: 1

Related Questions