Hello Experts My requirment is this. I have a master table say (MasterID, Recname1,RecName2...) I have a child table that looks like this (ChildID,MasterID,DateStamp,Description) My requirement is get all the master records and get the latest child record with datestamp and description. What is the best way to get this done.
To get the combined information, obviously you would use an INNER JOIN: SELECT m.RecName1, c.Description FROM MasterTable m INNER JOIN ChildTable c Now to get only the latest child row, you add a correlated subquery: WHERE c.ChildID = (SELECT TOP 1 x.ChildID FROM ChildTable x WHERE x.MasterID = c.MasterID ORDER BY x.DateStamp DESC)
Thanks Adriaan I have tried this query but it takes too long to process the query. I have created a Temp Table and loaded the records and filtered it into another temp table. This method improved the query process time. Yours took 1 min 45 secs and the Temp table method took 30 secs but I am looking for ways to even speed up the query. The child table has some million records. Thanks for your quick reply
Sounds like your child table doesn't have appropriate indexes. For instance PK clustered on (ChildID) and non-clustered on (MasterId, DateStamp).
Hi,Please try this and let me know if it helps. SELECT *FROM (SELECT M.*, C.ChildID, C.DateStamp, C.Description FROM MasterTable MINNER JOIN ChildTable CON M.MasterID = C.MasterID) AS SET1INNER JOIN (SELECT ChildID, MAX(DateStamp) AS MaxDateStamp FROM ChildTable GROUP BY ChildID) AS SET2ON SET1.ChildID = SET2.ChildIDAND SET1.DateStamp = SET2.MaxDateStamp
