Help in inner query [CTE] - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help in inner query [CTE]

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-03-19 : 11:37:23

I have a child table cqt in which i wanted to get top 1 record's cqtDestinationEntry in result set

I wanted to implemetn either by CTE or any thing

My query is

SELECT csm.fkSegmentID,
csm.csmContainerID,
csmContainerGrossWeight,
csmNumberofCopies,
cqt.cqtDestinationEntry,
row_number() over (partition by csm.fkSegmentID, csm.csmContainerID ORDER BY cqt.cqtDestinationEntry DESC) as Rn
FROM SegmentRecord seg
INNER JOIN ContainerSummaryRecord csm
ON seg.fkJobID = csm.fkJobID
AND seg.segSegmentID = csm.fkSegmentID
inner Join ContainerQuantityRecord cqt
on cqt.fkJobID = csm.fkJobID
and cqt.fkContainerID = csm.csmContainerID
where seg.fkJobID = 'omc00070'
and csmContainerType = 'P'

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-19 : 11:57:43

Define "TOP 1"

EDIT: Please don't say anything...based on the last one entered? The Most recent date of something? Post the Table.Column and data type that the TOP 1 refers too

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-19 : 14:18:39

based on this


...
row_number() over (partition by csm.fkSegmentID, csm.csmContainerID ORDER BY cqt.cqtDestinationEntry DESC) as Rn

...

if you put a where condition Rn=1 you will get record with max cqtDestinationEntry value within each csm.fkSegmentID, csm.csmContainerID group

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources