Select records based on Id and most updated record of the same Id

Question

I have a table Applications

The user can submit more than one application. The user can also update an existing application, but instead of updating the record itself, we will insert a new record with the same ApplicationNumber

Id  ApplicationNum    ApplicantId     ApplicantName  CreateDate

1    101              789              John          May-20-2021
2    101              789              John          May-21-2021
3    102              789              John          May-22-2021
4    103              123              Maria         May-31-2021

I want to return the list of applications based on the ApplicantId, but I don’t want to display both records of the same ApplicationNumber

If I use this select statement

Select * from Applications where ApplicantId = 789

This is the result I currently get

1    101              789              John          May-20-2021
2    101              789              John          May-21-2021
3    102              789              John          May-22-2021

This is the result I want to get

2    101              789              John          May-21-2021
3    102              789              John          May-22-2021

Notice that record Id = 1 is not displayed because it is an old version of record Id = 2

How can I achieve this?

Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, May 31 '21 at 10:38

score 0 · Answer 1 · answered May 31 '21 at 10:27

0

I like using ROW_NUMBER along with a TIES trick here:

SELECT TOP 1 WITH TIES *
FROM Applications
WHERE ApplicantId = 789
ORDER BY ROW_NUMBER() OVER (PARTITION BY ApplicantId, ApplicationNum ORDER BY Id DESC);

answered May 31 '21 at 10:27

Tim Biegeleisen

502,043
27
286
360

I know you like it, but it's inefficient compared to `WHERE rn = 1` as it requires a sort – Charlieface May 31 '21 at 10:39

score 0 · Answer 2 · answered May 31 '21 at 10:33

0

Might be easier to just use:

select max(Id) as Id, ApplicationNum, ApplicantId, ApplicantName, max(CreateDate) as CreateDate

from Applications

where ApplicantId = 789

group by ApplicationNum, ApplicantId, ApplicantName

answered May 31 '21 at 10:33

Derek Macrae

49
3

score 0 · Answer 3 · answered May 31 '21 at 10:36

The traditional way which is usually the most performant is to use row_number and select the desired row from each group of Applicants

select Id, ApplicationNum, ApplicantId, ApplicantName, CreateDate
from (
    select *, Row_Number() over(partition by ApplicantId, ApplicationNum order by Id desc) rn
    from Applications
    where ApplicantId=789
)a
where rn=1

Select records based on Id and most updated record of the same Id

3 Answers3