#SQLSERVERLearning : Order By Learning.

Most of folks feel , both(Oracle-SQLServer) are traditional RDBMS database and should or need to work some what “SIMILAR”.
But, But when we actually start working on core with queries, development and tuning.We identified a lot of difference between any two database.

All my blogs with #SQLSERVERLearning is intended to cover all stuff identified as difference between Oracle and SQL Server.
Hopefully it might be helpful for Oracle guy also working in SQL Server world.
All finding or Observations with SQL Server for any Oracle guy can be access here.

We had a requirement To have Custom Order on Varchar data as per numeric part in data.
For instance for below information.

select distinct ',' + QUOTENAME (c.b)
from (select 'p' + '1' as b
union all
select 'p' + '10'
union all
select 'p' + '2') as c
for xml path('') , type ;

Output was ,[p1],[p10],[p2] and our requirement was it should be on basis of numeric part i.e. ,[p1],[p2],[p10].

1. ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

We try to add Custom order ” Order by CAST(SUBSTRING(b,2,len(b)) as INT )” in same query but got a exception.

select distinct ',' + QUOTENAME (c.b)
from (select 'p' + '1' as b
union all
select 'p' + '10'
union all
select 'p' + '2') as c
order by cast (SUBSTRING(b,2,len(b)) as INT )
for xml path('') , type ;

Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Whenever we have DISTINCT, we can only order by based on column projected in output and can’t have any other Column or expression as part of order by.

below query will also not work,

select distinct ',' + QUOTENAME (c.b)
from (select 'p' + '1' as b
union all
select 'p' + '10'
union all
select 'p' + '2') as c
order by c.b
for xml path('') , type ;

It is tightly coupled with Expression/Column Projected as per Distinct Expression.

2.The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Then we try to Move Order by inline after Distinct and before XML PATH operation.
select ',' + QUOTENAME (c.b)
from
(select B from
((SELECT DISTINCT C.B
FROM (SELECT 'P' + '1' AS B
UNION ALL
SELECT 'P' + '10' AS Expr1
UNION ALL
SELECT 'P' + '2' AS Expr1
UNION ALL
SELECT 'P' + '2' AS Expr1
UNION ALL
SELECT 'P' + '3' AS Expr1
UNION ALL
SELECT 'P' + '22' AS Expr1) AS C)) as c order by cast (SUBSTRING(b,2,len(b)) as INT ) ) as c
for xml path('') , type ;

But it give exception and does not allow inline order by.

Exception :
Msg 1033, Level 15, State 1, Line 15
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

So thought to push order by inline by using “TOP 100 PERCENT”, it will cause to fetch all rows and adhere order by constraint.

select ',' + QUOTENAME (c.b)
from
(select TOP 100 PERCENT B from
((SELECT DISTINCT C.B
FROM (SELECT 'P' + '1' AS B
UNION ALL
SELECT 'P' + '10' AS Expr1
UNION ALL
SELECT 'P' + '2' AS Expr1
UNION ALL
SELECT 'P' + '2' AS Expr1
UNION ALL
SELECT 'P' + '3' AS Expr1
UNION ALL
SELECT 'P' + '22' AS Expr1) AS C)) as c order by cast (SUBSTRING(b,2,len(b)) as INT ) ) as c
for xml path('') , type ;

Output ::
,[P1],[P10],[P2],[P22],[P3]

Though we had order by, but same was completely ignored by SQL SERVER.
Order by is still based on Distinct operation. and provided Order by expression is ignored.

Underlying Execution plan has an explanation for same.Sort operation is ordering based on default expression projected for XML elements.

orderby1

Sorting is due to DISTINCT operation and not due to our custom provided Order by.
So though we have use TOP 100 PERCENT and Custom Ordering, it was completely ignore by SQL Server.

Final Solution :
Provide required ordering in XML scope and let it get applied after Default Distinct Order by.

select ',' + QUOTENAME (c.b)
from
(select B from
((SELECT DISTINCT C.B
FROM (SELECT 'P' + '1' AS B
UNION ALL
SELECT 'P' + '10' AS Expr1
UNION ALL
SELECT 'P' + '2' AS Expr1
UNION ALL
SELECT 'P' + '2' AS Expr1
UNION ALL
SELECT 'P' + '3' AS Expr1
UNION ALL
SELECT 'P' + '22' AS Expr1) AS C)) as c ) as c
order by cast (SUBSTRING(b,2,len(b)) as INT )
for xml path('') , type ;

Output ::
,[P1],[P2],[P3],[P10],[P22]

Execution Plan :

orderby2

Now we have Two Order by, one implicit as per Distinct and other for our custom ordering.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in SQL Server Learning and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s