CODEWITHSUNDEEP
sqlsubquerymaxaggregatesql-function
Austin
Austin

Reputation: 41

Using SQL , how do I select the max values when there is more than one with the same max value?

I am trying to select the maximum ImportDate for each person. For this person "Bob James" he should have the last 2 rows selected with ID 267311 and 267342. I need to see every column in the table but just the maximum importdate row or rows if they have more than one file that came in on the same date. I tried to create subquery and tie it back to a larger query but when I try to use max its only bringing back one row with 11-01-2024 importdate.

select MAX(importdate),
       firstname, lastname 
from ClmEligibility_Intake 
where lastname = 'james'
  and firstname = 'bob'
group by firstname, lastname

enter image description here

Also tried this subquery, not working. It should only bring back 2 IDs:

enter image description here

Upvotes: -2

Views: 91

Answers (2)

SQLpro
SQLpro

Reputation: 5171

I think this will help you :

SELECT MAX(importdate), 
       firstname, lastname,
       ROW_NUMBER() OVER(PARTITION BY firstname, lastname ORDER BY  ID DESC) AS N
FROM   ClmEligibility_Intake
WHERE  lastname = 'james'
       AND firstname = 'bob'
GROUP  BY firstname, lastname
ORDER BY N OFFSET 0 ROWS FECTH NEXT 2 ROWS ONLY

Upvotes: 0

BenderBoy
BenderBoy

Reputation: 1336

Easy way:

--get all the max(importdate)s per person in a CTE
;with CTE as (
    select maxdate = MAX(importdate)
        , firstname
        , lastname 
    from ClmEligibility_Intake
    group by firstname, lastname
)
--just select all the rows that have the same importdate as max(importdate)
select *
from ClmEligibility_Intake EI
    inner join CTE on CTE.lastname = EI.lastname
                  and CTE.firstname = EI.firstname
                  and CTE.maxdate = importdate

Better way:

select *
from ClmEligibility_Intake EI
    --self-join rows where a greater importdate exists:
    left join ClmEligibility_Intake X on X.lastname = EI.lastname
                                     and X.firstname = EI.firstname
                                     and X.importdate > EI.importdate 
where X.Id is null --only show rows for which no greater importdate was found

Same thing but you may find this more idiomatic:

select *
from ClmEligibility_Intake EI
where not exists (select *
                  from ClmEligibility_Intake X
                  where X.lastname = EI.lastname
                    and X.firstname = EI.firstname
                    and X.importdate > EI.importdate)

I call the table alias X to indicate it’s only pulled in to be thrown away.

If you insist on window functions:

select *
from (select *, YeOldeRank = rank() over (partition by lastname, lastname order by importdate desc)
      from ClmEligibility_Intake) EI
where YeOldeRank = 1

Upvotes: 2

Related Questions