Latest modified records

[Zoom1234]

Hi,

I have a table for each keyfield i have multiple records. i Want to get the latest modified records for each keyfield.

I am trying this

select S.keyfield, S.status, PT.producttemplatename  from SERVICE S, SERVICETEMPLATE ST, PRODUCTTEMPLATE PT
where S.keyfield in (1234, 5678)
and S.servicetemplateid = ST.servicetemplateid
and ST.producttemplateid = PT.producttemplateid
order by S.modifieddate desc limit 1  ;

but i am getting latest mofidied record among all keyfields..i.e. only single record.

How can i acheive the desired result.

 

[r937]

SELECT s.keyfield
     , s.status
     , pt.producttemplatename  
  FROM service AS s
INNER
  JOIN ( SELECT keyfield
              , MAX(modifieddate) AS maxdate
           FROM service
         GROUP
             BY keyfield ) AS m
    ON m.keyfield = s.keyfield
   AND m.maxdate = s.modifieddate
INNER
  JOIN servicetemplate AS st
    ON st.servicetemplateid = s.servicetemplateid
INNER
  JOIN producttemplate AS pt
    ON pt.producttemplateid = st.producttemplateid
 WHERE s.keyfield IN (1234,5678)

r937.com | rudy.ca

 

[Zoom1234]

Thanks r937.

For the same report I have to acheive following. I have SERVICE table and FEATURE table. For each service there are number of features in feature table. The FEATURE table is having a column as 'modifieddate'and 'status'. How can i get a latest status of each feature for given set of services (ids).

Thanks in advance.

 

[Zoom1234]

Hi,
Can someone help please..? I am stucked..