Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003
    Posts
    60

    Unanswered: Query Optimization

    IS there any way to rewrite this Query in optimized way?

    SELECT dbo.Table1.EmpId E from dbo.Table1
    where EmpId in(
    SELECT dbo.Table1.EmpId
    FROM (SELECT DISTINCT PersonID, MAX(dtmStatusDate) AS dtmStatusDate
    FROM dbo.Table1
    GROUP BY PersonID) derived_table INNER JOIN
    dbo.Table1 ON derived_table.PersonID = dbo.Table1.PersonID AND
    derived_table.dtmStatusDate = dbo.Table1.dtmStatusDate))

    Thanks....j

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Don't know abiut being faster but I think this is what oyu are trying to do. (get the empid's with max(dtmStatusDate) from each person.


    SELECT t1.EmpId
    from dbo.Table1 t1
    where t1.dtmStatusDate =
    (select max(dtmStatusDate) from dbo.Table1 t2 where t1.PersonID = t2.PersonID)

    also try

    SELECT t1.EmpId
    from dbo.Table1 t1
    where not exists ( select * from dbo.Table1 t2 where t1.PersonID = t2.PersonID and t1.dtmStatusDate < t2.dtmStatusDate)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •