cte ORDERING bizarreness - 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 2012 Forums

Transact-SQL (2012)

cte ORDERING bizarreness

Author

Topic

pbannist
Starting Member

3 Posts

Posted - 2014-10-03 : 03:59:47

Heres my query:

;with cte as (
SELECT RMT_Baseline_Id,
B1.CIIdentifier,
RMT_ParentBaselineId,
cast('' as varchar(max)) as [path], 0 as level,
row_number()over(partition by RMT_ParentBaselineId
order by B1.CIIdentifier desc) / power(10.0,0) as x
FROM tbl_RMTRiskIssue R1
INNER JOIN tbl_Baseline B1 on B1.Id=R1.RMT_Baseline_Id
WHERE RMT_ParentBaselineId is null
UNION ALL
SELECT t.RMT_Baseline_Id,
B2.CIIdentifier,
t.RMT_ParentBaselineId,
[path]+B2.CIIDentifier+'-->',
level+1,
x + row_number()over(partition by t.RMT_ParentBaselineId
ORDER BY B2.CIIdentifier desc) / power(10.0,level+1)
FROM cte
JOIN tbl_RMTRiskIssue t on cte.RMT_Baseline_Id = t.RMT_ParentBaselineId
INNER JOIN tbl_Baseline B2 on B2.Id=cte.RMT_Baseline_Id )
SELECT x, b.ciidentifier,[path] from cte
INNER JOIN tbl_Baseline B on B.Id =cte.RMT_Baseline_id
WHERE B.thisbaseline IN(615)
ORDER BY x

Here's my results

x CIIdentifier path
694.100000000000000000 RA.0016 RI.0001-->
694.110000000000000000 RA.0008 RI.0001-->RA.0016-->
694.120000000000000000 RA.0015 RI.0001-->RA.0016-->

Can any one tell me why line 3 doesnt come before line 2? Its supposed to sort by CIIdentifier DESC but its not

I think x needs to be constructed differently for the children so 694.11 is RA.0015 and 694.12 is RA.0008 but cant work it out, the CTE is ordered by CIIdentifier desc but seems to be ignored, if I change it to ASC then the results are the same which Im finding bizarre

why dont get x=694.11 for RA.0008 and 694.12 for RA.0015 when the order by is CIIDENTFIER DESC?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-03 : 09:19:40

First, I would run the individual queries separately to be sure they return what I want. Then I would combine them. This approach usually shows my problem.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-10-04 : 15:37:37

"ORDER BY x" is correct! The most outer join determines the output.
694.10
694.11
694.12

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources