surendiran.balasubramanian (4/19/2013)
how to convert the below subquery into join ..SELECT CtryId, WrkflwId, Dt
FROM stepHistory H
WHERE EXISTS
(SELECT * FROM
(SELECT WrkflwId, CtryId , max(DtTm) DtTm
FROM stepHistory
GROUP BY WrkflwId, ctryId
) S
WHERE S.WrkflwId = H.WrkflwId AND S.DtTm = H.DtTm AND H.CtryId = S.CtryId)
AND SteTyId = 2
----I have use the above query as inner join because i have to run the above query into an datawarehous applicance which wont support subquery
please help
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
If the row in the inner query having the maximum date per CtryId and WrkflwId has a different SteTyId to the outer query, the outer query will drop the row unless the row with SteTyId = 2 has the same value for DtTm. Are you absolutely sure this query does what you are expecting it to?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden