ORDER BY clause and function with alias column name in SQL Server

ORDER BY clause is really tricky sometime.  It doesn’t accept alias column name in some situation nor it is being used in Subquery. However we do have some alternate way with the trick to do so.

There was small discussion going about this on Vijaya’s Blog Where I have participated.  .NET MVP Vijaya’s raised the question about this topic and I found it interesting that is why I am sharing it here.

Basically he asked readers that why Order By is working with column alias

For eg:

SELECT
LoginID,
HireDate,
GETDATE() AS Current_Dt
FROM HumanResources.Employee
ORDER BY Current_Dt
And why the same alias doesn’t work with the function in ORDER BY clause.

For eg:

SELECT
LoginID,
HireDate,
GETDATE() AS Current_Dt
FROM HumanResources.Employee
ORDER BY ABS(DATEDIFF(DAY,Current_Dt,HireDate));

I found one trick with CTE which can work for this:

For Eg:

with cte as
(
SELECT
LoginID,
HireDate,
GETDATE() AS Current_Dt
FROM HumanResources.Employee
)select loginid,hiredate,current_dt from cte
ORDER BY ABS(DATEDIFF(DAY,Current_Dt,HireDate))

I would like my blog reader to participate there. Here is the link for that article. DotNetVJ

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Advertisements

Interesting observation about VIEW and ORDER BY clause in SQL Server 2008

View is been always interesting topic for me and for many of the SQL professionals. Use of ORDER BY clause inside the view is really a question of debate since long. I am going to present my observation about view in this article which I have tested in SQL Server 2008 (10.0.1600.22 ((SQL_PreRelease).080709-1414 ))

It is not guaranteed that it will remain same in all versions of SQL Server.

Basically, you can’t create VIEW with ORDER BY clause. However, if you will use TOP clause inside your SELECT clause, you can specify ORDER BY clause.

NOTE: why Order by is not working with VIEW and why it is working with TOP clause is also an interesting topic which I will discuss sometime later.

Anyway, let us go through some small practical code snippet which is really basic but useful to make fundamental clear.

CREATE TABLE ViewTest
(
      col1 CHAR(1)
)
GO
INSERT INTO ViewTest
SELECT ‘A’ UNION ALL
SELECT ‘C’ UNION ALL
SELECT ‘D’ UNION ALL
SELECT ‘B’ UNION ALL
SELECT ‘Z’ UNION ALL
SELECT ‘R’
GO
select Col1 from ViewTest order by col1
GO
–Result of above query
–Col1
——
–A
–B
–C
–D
–R
–Z
–(6 row(s) affected)
CREATE VIEW vViewTest AS
select Col1 from ViewTest order by col1
GO
–creation of this view won’t work rather,
–you will be greeted with following error
–Msg 1033, Level 15, State 1, Procedure vViewTest, Line 2
–The ORDER BY clause is invalid in views, inline functions, derived tables,
–subqueries, and common table expressions, unless TOP or FOR XML is also specified.
–you can’t use ORDER BY clause in View that’s why you faced an error.
–even if you will try use TOP clause, you can specify ORDER BY
CREATE VIEW vViewTest AS
select TOP 100 PERCENT Col1 from ViewTest order by col1
GO
SELECT * FROM vViewTest
GO
–result of above query
–Col1
——
–A
–C
–D
–B
–Z
–R
–(6 row(s) affected)
Result is not sorted, however it was working in SQL Server 2000 but right now it is not working in SQL Server 2005+ versions.

Let us try one more thing here.

ALTER VIEW vViewTest AS
select TOP 99.999 PERCENT Col1 from ViewTest order by col1
GO
SELECT * FROM vViewTest
GO
–result of above query is.
–Col1
——
–A
–B
–C
–D
–R
–Z
–(6 row(s) affected)
Bingo!!! it is working now but this is I guess error or something so I recommend not to trust this 99.999 stuff and simply use following solution for ordering your view. If you right now use “99.999” stuff and in next patches or in next fixes, it might stop working but if you will ordering your result set while executing select, it is guaranteed that ordering will work 100% sure. For eg:

ALTER VIEW vViewTest AS
select Col1 from ViewTest
GO
SELECT * FROM vViewTest order by col1
GO

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Interesting observation about VIEW and ORDER BY clause in SQL Server 2008

View is been always interesting topic for me and for many of the SQL professionals. Use of ORDER BY clause inside the view is really a question of debate since long. I am going to present my observation about view in this article which I have tested in SQL Server 2008 (10.0.1600.22 ((SQL_PreRelease).080709-1414 ))

It is not guaranteed that it will remain same in all versions of SQL Server.

Basically, you can’t create VIEW with ORDER BY clause. However, if you will use TOP clause inside your SELECT clause, you can specify ORDER BY clause.

NOTE: why Order by is not working with VIEW and why it is working with TOP clause is also an interesting topic which I will discuss sometime later.

Anyway, let us go through some small practical code snippet which is really basic but useful to make fundamental clear.

CREATE TABLE ViewTest
(
      col1 CHAR(1)
)
GO
INSERT INTO ViewTest
SELECT ‘A’ UNION ALL
SELECT ‘C’ UNION ALL
SELECT ‘D’ UNION ALL
SELECT ‘B’ UNION ALL
SELECT ‘Z’ UNION ALL
SELECT ‘R’
GO
select Col1 from ViewTest order by col1
GO
–Result of above query
–Col1
——
–A
–B
–C
–D
–R
–Z
–(6 row(s) affected)
CREATE VIEW vViewTest AS
select Col1 from ViewTest order by col1
GO
–creation of this view won’t work rather,
–you will be greeted with following error
–Msg 1033, Level 15, State 1, Procedure vViewTest, Line 2
–The ORDER BY clause is invalid in views, inline functions, derived tables,
–subqueries, and common table expressions, unless TOP or FOR XML is also specified.
–you can’t use ORDER BY clause in View that’s why you faced an error.
–even if you will try use TOP clause, you can specify ORDER BY
CREATE VIEW vViewTest AS
select TOP 100 PERCENT Col1 from ViewTest order by col1
GO
SELECT * FROM vViewTest
GO
–result of above query
–Col1
——
–A
–C
–D
–B
–Z
–R
–(6 row(s) affected)
Result is not sorted, however it was working in SQL Server 2000 but right now it is not working in SQL Server 2005+ versions.

Let us try one more thing here.

ALTER VIEW vViewTest AS
select TOP 99.999 PERCENT Col1 from ViewTest order by col1
GO
SELECT * FROM vViewTest
GO
–result of above query is.
–Col1
——
–A
–B
–C
–D
–R
–Z
–(6 row(s) affected)
Bingo!!! it is working now but this is I guess error or something so I recommend not to trust this 99.999 stuff and simply use following solution for ordering your view. If you right now use “99.999” stuff and in next patches or in next fixes, it might stop working but if you will ordering your result set while executing select, it is guaranteed that ordering will work 100% sure. For eg:

ALTER VIEW vViewTest AS
select Col1 from ViewTest
GO
SELECT * FROM vViewTest order by col1
GO

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com