Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to convert the below subquery to joins Expand / Collapse
Author
Message
Posted Friday, April 19, 2013 2:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 22, 2014 3:07 AM
Points: 3, Visits: 19
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


--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Post #1444270
Posted Friday, April 19, 2013 2:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:32 AM
Points: 2,651, Visits: 4,731
Something like this

SELECT	H.CtryId, H.WrkflwId, H.Dt
FROM stepHistory H
INNER JOIN (
SELECT WrkflwId, ctryId, MAX( DtTm ) AS DtTm
FROM stepHistory
GROUP BY WrkflwId, ctryId
) AS SH ON H.WrkflwId = SH.WrkflwId AND H.CtryId = SH.CtryId AND H.DtTm = SH.DtTm
WHERE H.SteTyId = 2




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1444277
Posted Friday, April 19, 2013 3:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 6,719, Visits: 13,824
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?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1444289
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse