CODEWITHSUNDEEP
sql
RhysPickett
RhysPickett

Reputation: 17

SQL MAX Query Multiple Columns

Trying to populate multiple columns based on one MAX value but the grouping returns multiple results. Is there a way I can tell SQL to only pull the values based on the MAX that I want?

Query:

Select a.ID, (MAX)a.PayDate, a.AgencyName
From a
Group By a.ID, a.AgencyName

What I need is the latest paydate per ID, then I want additional information in reference to that entry such as AgencyName (& more columns I want to add) but because of the grouping - SQL returns the latest paydate for each of the AgencyNames that the person has had - but I only want the AgencyName associated with the record that is Max Paydate for that ID. I know it's the grouping that does this but I am unsure how to proceed - any help greatly appreciated.

Thanks

Upvotes: 1

Views: 364

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I would just use a correlated subquery like this:

select a.*
from a
where a.paydate = (select max(a2.paydate) from a a2 where a2.id = a.id);

Note that this could return multiple rows if an id has duplicates on the most recent paydate. An alternative that guarantees one row is row_number():

select a.*
from (select a.*,
             row_number() over (partition by id order by paydate desc) as seqnum
      from a
     ) a
where seqnum = 1;

Upvotes: 0

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30565

Select a.ID,a.PayDate, a.AgencyName
From a
where exists (select 1 from a a1 where a1.id = a.id 
  having a.payDate = max(a1.paydate)
Group By a.ID,

Upvotes: 1

Related Questions