max date with specific criteria from same table

3OfHarts · #1 (**permalink**) 09-23-09, 13:01

I am having a problem selecting records whose status code = 'N' only if it is the record with the lastest date. In other words if I have multiple records for the same person that looks like this:

ID STATUS_CODE ACTIVITY_DATE
1234 I 14-AUG-2009
1234 N 1-AUG-2009

I wouldn't want this person on my list because the latest date doesn't have a status of N. Instead I am getting everyone with an N regardless of whether or not it's the lastest record. I have tried several different scenarios with no luck. This is what I have that isn't working. I have this embedded in with other joins and selection criteria, but this is the part that doesn't seem to be working. I am still fairly new to SQL, so don't be too hard on me.

select * from (select tzrstsf_pidm,tzrstsf_activity_date, tzrstsf_status_code, tzrstsf_term
from tzrstsf,
(select max(z.tzrstsf_activity_date)as maxdate from tzrstsf z group by z.tzrstsf_pidm) maxresults
where tzrstsf_term = '200980' and tzrstsf_activity_date = maxresults.maxdate)
where tzrstsf_status_code = 'N';

gvee · #2 (**permalink**) 09-23-09, 14:19

Max date per id:

Code:

SELECT id
     , Max(activity_date)
FROM   your_table
GROUP
    BY id

Join back to self to get status_code

Code:

SELECT a.id
     , a.status_code
     , a.activity_date
FROM   your_table As a
 INNER
  JOIN (
        SELECT id
             , Max(activity_date) As b.max_activity_date 
        FROM   your_table
        GROUP
            BY id
       ) As b
    ON b.id = a.id
   AND b.max_activity_date = a.activity_date

Final where clause:

Code:

SELECT a.id
     , a.status_code
     , a.activity_date
FROM   your_table As a
 INNER
  JOIN (
        SELECT id
             , Max(activity_date) As b.max_activity_date 
        FROM   your_table
        GROUP
            BY id
       ) As b
    ON b.id = a.id
   AND b.max_activity_date = a.activity_date
WHERE  a.status_code = 'N'

3OfHarts · #3 (**permalink**) 09-23-09, 15:16

This is what I tried and it gave me an error message sql command not properly ended on the second line.

select a.tzrstsf_pidm, a.tzrstsf_status_code, a.tzrstsf_activity_date
from tzrstsf as a
inner join (select tzrstsf_pidm,
max(tzrstsf_activity_date) as b.max_activity_date
from tzrstsf
group by tzrstsf_pidm) as b
on b.tzrstsf_pidm = a.tzrstsf_pidm
and b.max_activity_date = a.tzrstsf_activity_date
where a.tzrstsf_status_code = 'N'

Internet Services

Web Development

Digital Marketing

Consumer Tech