How To Query for Two MAXs in the Same Query?

[txdave35]

Hey guys,

I am trying to write a query that will return each employee's YTD balance. The problem is in the table, there are multiple YTD balances each one occuring in a specific year and month number. I want to only retrieve the most recent YTD balance. A sample of the table would look like this:


EMP # ANTY_YY ANTY_MM YTD BALANCE

001 2014 12 12,444.31
001 2015 01 13,444.31
001 2015 02 14,444.31


So basically I need to two MAX returns. First one to return the highest year which is 2015. Then one that will return the MAX ANTY_MM for that year and show the balance on that record.
I can get the first MAX to work and give me the latest year, but it shows me all the month records in that year. I can't get my query to show me the MAX month as well.

select a.recip_ssn_nbr,
       a.anty_yy,
       a.anty_mm,
       a.drop_ytd_amt
       


from

(SELECT   a.RECIP_SSN_NBR,
         a.ANTY_YY,
         a.ANTY_MM,
         a.DROP_YTD_AMT
FROM     DSNP.PR01_T_DROP_AMTS a
WHERE    a.RECIP_SSN_NBR = 111223333
and      a.anty_yy = (select max(b.anty_yy) from dsnp.pr01_t_drop_amts b
                       where b.recip_ssn_nbr = a.recip_ssn_nbr) ) as A

where a.anty_mm = (select max(b.anty_mm) from dsnp.pr01_t_drop_amts b
                     where b.recip_ssn_nbr = a.recip_ssn_nbr)

When I try this query, it gets me zero rows. Any idea what I'm doing wrong?? Thanks!

 

[DaveInIowa]

SELECT TOP 1 *
  FROM dsnp.pr01_t_drop_amts
 ORDER BY anty_yy DESC, anty_mm DESC

 

[bborissov]

DECLARE @Test TABLE (EMPNo Char(3), ANTY_YY int, ANTY_MM int, YTD_BALANCE numeric(12,2))
INSERT INTO @Test VALUES('001',2014, 12, 12444.31)
INSERT INTO @Test VALUES('001',2015, 01, 13444.31)
INSERT INTO @Test VALUES('001',2015, 02, 14444.31)

INSERT INTO @Test VALUES('002',2014, 12, 12444.31)
INSERT INTO @Test VALUES('002',2015, 01, 13444.31)
INSERT INTO @Test VALUES('002',2016, 02, 20000.31)

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY EmpNo ORDER BY  ANTY_YY DESC, YTD_BALANCE DESC) AS RC
       From @Test) test
WHERE RC = 1

Borislav Borissov
VFP9 SP2, SQL Server

 

[Olaf Doschke]

For a specific EmpNo it's even simpler, again with Boris test data example:

DECLARE @Test TABLE (EMPNo Char(3), ANTY_YY int, ANTY_MM int, YTD_BALANCE numeric(12,2))
INSERT INTO @Test VALUES('001',2014, 12, 12444.31)
INSERT INTO @Test VALUES('001',2015, 01, 13444.31)
INSERT INTO @Test VALUES('001',2015, 02, 14444.31)

INSERT INTO @Test VALUES('002',2014, 12, 12444.31)
INSERT INTO @Test VALUES('002',2015, 01, 13444.31)
INSERT INTO @Test VALUES('002',2016, 02, 20000.31)

Select Top 1 * from @Test Where EmpNo = '001' ORDER BY ANTY_YY DESC, ANTY_MM DESC

Well, about what Dave already gave you, just filtered for a certain EmpNo, or in your case RECIP_SSN_NBR.

In your way of first extracting a certain year data, then the max amount, you have a problem reusing alias A, and also you retrieve max(year) and max(month) from all data in both queries, that will likely yield december of current year, no record, if you fix the alias issue. Because while max year is max year of all data. Max month of all data will stay december once you had any december data.

Bye, Olaf.

 

[Olaf Doschke]

In general, if you want the max of a subset of data, you have to take it from that subset:

DECLARE @Test TABLE (EMPNo Char(3), ANTY_YY int, ANTY_MM int, YTD_BALANCE numeric(12,2))
INSERT INTO @Test VALUES('001',2014, 12, 12444.31)
INSERT INTO @Test VALUES('001',2015, 01, 13444.31)
INSERT INTO @Test VALUES('001',2015, 02, 14444.31)

INSERT INTO @Test VALUES('002',2014, 12, 12444.31)
INSERT INTO @Test VALUES('002',2015, 01, 13444.31)
INSERT INTO @Test VALUES('002',2015, 02, 20000.31)

--Select all data of the latest year
Select * from @Test a Where anty_yy = (select max(b.anty_yy) from @Test b)

--Select latest month of latest year data
select max(a.anty_mm) from @Test a Where anty_yy = (select max(b.anty_yy) from @Test b)

--Select latest month data of latest year data
Select * From @Test a
Where a.anty_yy = (select max(b.anty_yy) from @Test b)
 and  a.anty_mm = (select max(aa.anty_mm) from @Test aa Where anty_yy = (select max(bb.anty_yy) from @Test bb))

The core max of max query is (select max(aa.anty_mm) from @Test aa Where anty_yy = (select max(bb.anty_yy) from @Test bb)). Also notice within each query I didn't reuse any table alias a, b, aa, bb, that makes addressing tables and fields unambiguous.

It gets much simpler going the path of ORDERing data nested/partitioned and taking max of that via taking top row or rows (per partition).

Bye, Olaf.