Query

Question

Query

VSSGeorge

SSCrazy Eights

Points: 8151
More actions
January 6, 2015 at 12:05 am

#294053

I have a query like :
SELECT DISTINCT C1.containerCode ,C1.containerName,
(SELECT C1.containerCode as containerCode,SE.createdOn
FROM Containers C1
LEFT JOIN StoringsEvents SE ON C1.containerIncId = SE.ITEMINCID
AND C1.containerSqlId = SE.ITEMSQLID
AND SE.isDeleted = 0
LEFT JOIN CONTAINERS C2 ON C2.CONTAINERINCID = SE.CONTAINERINCID
AND C2.CONTAINERSQLID = SE.CONTAINERSQLID
AND C2.isDeleted = 0 AND C1.isDeleted =0
inner join
( select C1.containerCode, MAX(SE.createdOn) as maxdate
FROM Containers C1
LEFT JOIN StoringsEvents SE ON C1.containerIncId = SE.ITEMINCID
AND C1.containerSqlId = SE.ITEMSQLID
AND SE.isDeleted = 0
LEFT JOIN CONTAINERS C2 ON C2.CONTAINERINCID = SE.CONTAINERINCID
AND C2.CONTAINERSQLID = SE.CONTAINERSQLID
AND C2.isDeleted = 0
WHERE C1.isDeleted = 0
group by C1.containerCode) CC
on CC.containerCode = C1.containerCode
and CC.maxdate = SE.createdOn) AS NEWLOCATION
----------------
For the column, I need to show only the field C1.containerCode as NEWLOCATION and SE.createdOn is not required. But it is used there, to fetch the record with max createdOn date. How can I fetch the fetch the C1.containerCode as NEWLOCATION and show it

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Please provide table DDL, some sample data and desired output.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Junglee_George (1/6/2015)
I have a query like :
SELECT DISTINCT C1.containerCode ,C1.containerName,
(SELECT C1.containerCode as containerCode,SE.createdOn
FROM Containers C1
LEFT JOIN StoringsEvents SE ON C1.containerIncId = SE.ITEMINCID
AND C1.containerSqlId = SE.ITEMSQLID
AND SE.isDeleted = 0
LEFT JOIN CONTAINERS C2 ON C2.CONTAINERINCID = SE.CONTAINERINCID
AND C2.CONTAINERSQLID = SE.CONTAINERSQLID
AND C2.isDeleted = 0 AND C1.isDeleted =0
inner join
( select C1.containerCode, MAX(SE.createdOn) as maxdate
FROM Containers C1
LEFT JOIN StoringsEvents SE ON C1.containerIncId = SE.ITEMINCID
AND C1.containerSqlId = SE.ITEMSQLID
AND SE.isDeleted = 0
LEFT JOIN CONTAINERS C2 ON C2.CONTAINERINCID = SE.CONTAINERINCID
AND C2.CONTAINERSQLID = SE.CONTAINERSQLID
AND C2.isDeleted = 0
WHERE C1.isDeleted = 0
group by C1.containerCode) CC
on CC.containerCode = C1.containerCode
and CC.maxdate = SE.createdOn) AS NEWLOCATION
----------------
For the column, I need to show only the field C1.containerCode as NEWLOCATION and SE.createdOn is not required. But it is used there, to fetch the record with max createdOn date. How can I fetch the fetch the C1.containerCode as NEWLOCATION and show it

This isn't a properly-formed query. It looks like you are attempting to retrieve the last-known position of a container from table StoringsEvents, is this correct?

^{“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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

More readable:

SELECT DISTINCT

C1.containerCode,

C1.containerName,

(SELECT C1.containerCode AS containerCode,

SE.createdOn

FROM Containers C1

LEFT JOIN StoringsEvents SE ON C1.containerIncId = SE.ITEMINCID

AND C1.containerSqlId = SE.ITEMSQLID

AND SE.isDeleted = 0

LEFT JOIN CONTAINERS C2 ON C2.CONTAINERINCID = SE.CONTAINERINCID

AND C2.CONTAINERSQLID = SE.CONTAINERSQLID

AND C2.isDeleted = 0

AND C1.isDeleted = 0

INNER JOIN (SELECT C1.containerCode,

MAX(SE.createdOn) AS maxdate

FROM Containers C1

LEFT JOIN StoringsEvents SE ON C1.containerIncId = SE.ITEMINCID

AND C1.containerSqlId = SE.ITEMSQLID

AND SE.isDeleted = 0

LEFT JOIN CONTAINERS C2 ON C2.CONTAINERINCID = SE.CONTAINERINCID

AND C2.CONTAINERSQLID = SE.CONTAINERSQLID

AND C2.isDeleted = 0

WHERE C1.isDeleted = 0

GROUP BY C1.containerCode

) CC ON CC.containerCode = C1.containerCode

AND CC.maxdate = SE.createdOn

) AS NEWLOCATION

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

VSSGeorge SSCrazy Eights Points: 8151 More actions · Answer 4

Yes Chris, You are correct. I want to get last position from StoringEvents Table.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Junglee_George (1/6/2015)
Yes Chris, You are correct. I want to get last position from StoringEvents Table.

Thanks. Can you follow Koen's advice and post scripts please? Also, can you provide us with a definition of the following columns:

StoringsEvents.ITEMINCID

StoringsEvents.ITEMSQLID

StoringsEvents.CONTAINERINCID

StoringsEvents.CONTAINERSQLID

Containers.CONTAINERINCID

Containers.CONTAINERSQLID

Cheers.

^{“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

ScottPletcher SSC Guru Points: 101099 More actions · Answer 6

If the query otherwise works the way you want it to, just remove the unneeded column from the subquery:

...,

(SELECT C1.containerCode as containerCode --,SE.createdOn

FROM Containers C1

LEFT JOIN StoringsEvents SE ON C1.containerIncId = SE.ITEMINCID

AND C1.containerSqlId = SE.ITEMSQLID

AND SE.isDeleted = 0

LEFT JOIN CONTAINERS C2 ON C2.CONTAINERINCID = SE.CONTAINERINCID

AND C2.CONTAINERSQLID = SE.CONTAINERSQLID

AND C2.isDeleted = 0 AND C1.isDeleted =0

inner join

( select C1.containerCode, MAX(SE.createdOn) as maxdate

FROM Containers C1

LEFT JOIN StoringsEvents SE ON C1.containerIncId = SE.ITEMINCID

AND C1.containerSqlId = SE.ITEMSQLID

AND SE.isDeleted = 0

LEFT JOIN CONTAINERS C2 ON C2.CONTAINERINCID = SE.CONTAINERINCID

AND C2.CONTAINERSQLID = SE.CONTAINERSQLID

AND C2.isDeleted = 0

WHERE C1.isDeleted = 0

group by C1.containerCode) CC

...

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".