Query based on dates and MAX values, help greatly appreciated-CodePudding

I have this problem on my hands that I cannot quite figure out the best way to solve. We have a database of students and their study entitlements. Each student has unique studentId and for each student there can be 1-N amount of study entitlements, some might have over 10 for example. Each entitlement has an end date.

So what we would like to achieve is a query that results one row per student with the following conditions. I am using MS SQL.

if student has only one entitlement, return that
if they have multiple, return the one with furthest end date
if multiple entitlements have the same MAX end date, return the one with largest entitlement number

In the example below, ideally it would return the following rows:

Row 2 for Aaron A

Row 4 for Charlie C

Row 5 for Frank F

Any ideas are greatly appreciated!

row	studentId	studentName	entitlementId	entitlementEndDate
1	100	Aaron A	100500	2021-12-31
2	100	Aaron A	200600	2021-12-31
3	200	Charlie C	300800	2021-10-31
4	200	Charlie C	400900	2022-01-31
5	300	Frank F	500100	2022-09-31
6	300	Frank F	500900	2021-02-31

CodePudding user response：

Using ROW_NUMBER:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY studentId
                                   ORDER BY entitlementEndDate DESC, entitlementId DESC) rn
    FROM yourTable t
)

SELECT row, studentId, studentName, entitlementId, entitlementEndDate
FROM cte
WHERE rn = 1;

CodePudding user response：

First of all 2022-09-31 and 2021-02-31 are quite strange dates... so I will substitute them for 2022-09-30 and 2021-02-28.

That said, your query should be:

with cte (rowId, [row], studentId, studentName, entitlementId, entitlementEndDate)
as
(
    select ROW_NUMBER() over (partition by studentId order by entitlementEndDate desc,entitlementId desc),* from MyTable
)
select [row], studentId, studentName, entitlementId, entitlementEndDate from cte where rowId = 1

This would return: | row | studentId |studentName | entitlementId | entitlementEndDate | | ----------- | ----------- | -------------------------------------------------- | ------------- | ------------------ | | 2 | 100 | Aaron A |200600 |2021-12-31| | 4 | 200 | Charlie C |400900 |2022-01-31| | 5 |300 |Frank F |500100 | 2022-09-30|

You can check it with this code (it would be useful for future questions if you paste this code directly):

create table #MyTable (
    [row] int,
    studentId int,
    studentName varchar(50), 
    entitlementId   int,
    entitlementEndDate date
)

insert into #MyTable values (1, 100, 'Aaron A', 100500, '2021-12-31'),
(2  ,100    ,'Aaron A', 200600, '2021-12-31'),
(3  ,200    ,'Charlie C',   300800, '2021-10-31'),
(4  ,200    ,'Charlie C',   400900, '2022-01-31'),
(5  ,300    ,'Frank F', 500100, '2022-09-30'),
(6  ,300    ,'Frank F', 500900, '2021-02-28');

with cte (rowId, [row], studentId, studentName, entitlementId, entitlementEndDate)
as
(
    select ROW_NUMBER() over (partition by studentId order by entitlementEndDate desc,entitlementId desc),* from #MyTable
)
select [row], studentId, studentName, entitlementId, entitlementEndDate from cte where rowId = 1