INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How To Query for Two MAXs in the Same Query?

How To Query for Two MAXs in the Same Query?

(OP)
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.


CODE

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!



RE: How To Query for Two MAXs in the Same Query?

CODE

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

RE: How To Query for Two MAXs in the Same Query?

CODE

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

RE: How To Query for Two MAXs in the Same Query?

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

CODE

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.

RE: How To Query for Two MAXs in the Same Query?

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

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close