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

admin管理员组

文章数量:1125588

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

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

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

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

• sql
• subquery
• max
• aggregate
• sql-function

Share Improve this question Follow edited yesterday jarlh 44.7k88 gold badges5050 silver badges6767 bronze badges asked 2 days ago Austin Austin 4555 bronze badges 9

• There are too many columns in your example. Please include only the columns that matter (importdate, firstname, lastname). – Bart McEndree Commented 2 days ago
• I created this fiddle to help us collaborate on a solution dbfiddle.uk/RNCLSx7X – Bart McEndree Commented 2 days ago
• I need to see every column in solution. – Austin Commented 2 days ago
• Use a RANK() windows function. – Isolated Commented 2 days ago
• Never used rank. I just looked it up, and that doesnt make any sense to me. – Austin Commented 2 days ago

 |  Show 4 more comments

2 Answers 2

Sorted by: Reset to default 2

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

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

本文标签：