Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How Do You Find the Max in a Sub-Set?

Status
Not open for further replies.

VAST39

Programmer
Sep 21, 2012
26
US
Hey guys,

I am having trouble getting my query to select the max date for a specific record value. Here is an example of the table



NAME AGENCY HISTORY DATE

TEST 00922 2014-02-12
TEST 90212 2012-02-12


I want my query to return the last history date occurring only on agencies that start with 9. So in my example table, the date should be 2012-02-12.


But this query fails:


Code:
SELECT   A.MBR_SSN_NBR,
         A.MBR_HIST_SVC_CR_DT




FROM    DSNP.PR01_T_MBR_HIST A



where     left(A.agty_id_cd, 1) = '9'
and A.MBR_HIST_SVC_CR_DT = (select max(c.MBR_HIST_SVC_CR_DT) from dsnp.pr01_t_mbr_hist c where c.mbr_ssn_nbr = A.mbr_ssn_nbr)


Wouldn't SQL evaluate both conditions? I get blank records when I run it so my logic must be way off.






 
WHERE condition1 AND condition2 evaluates both conditions, yes. But condition2 is a subquery:

A.MBR_HIST_SVC_CR_DT = select max(c.MBR_HIST_SVC_CR_DT) from dsnp.pr01_t_mbr_hist c where c.mbr_ssn_nbr = A.mbr_ssn_nbr

In this subquery, the condition1 is NOT used, as condition 1 is on alias A, not on alias C and the indirect join you do is on mbr_ssn_nbr, which means ALL records with the same mbr_ssn_nbr will be taken into account to find the MAX(c.MBR_HIST_SVC_CR_DT)

If you would want to apply the same condition1 on c you'd have to repeat that in your subquery.

This is just to explain what happened, ideally you do what imex suggests, it's a simple group by, no need for a subquery.

Bye, Olaf.
 
something like that

SQL:
;with t as
(
SELECT   *,
         ROW_NUMBER() over(partition by A.MBR_SSN_NBR order by A.MBR_HIST_SVC_CR_DT desc) as rownum 
FROM    DSNP.PR01_T_MBR_HIST A
where     left(A.agty_id_cd, 1) = '9'
)
SELECT   *
FROM    t
where rownum = 1
 
Thanks guys for clearing up for me! Makes sense now
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top