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

[VAST39]

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:

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.

 

[imex]

Hi,

I do not know if I understood correctly but try:

SELECT
    MBR_SSN_NBR,
    max(MBR_HIST_SVC_CR_DT) as MBR_HIST_SVC_CR_DT
FROM DSNP.PR01_T_MBR_HIST
where 
    left(agty_id_cd, 1) = '9'
group by
    MBR_SSN_NBR

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[Olaf Doschke]

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.

 

[gk53]

something like that

;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

 

[VAST39]

Thanks guys for clearing up for me! Makes sense now