:::: MENU ::::

Using integers in order by clause of T-SQL

  • Mar 19 / 2009
  • 0
dbDigger, T-SQL Interview Questions, T-SQL Tips and Tricks

Using integers in order by clause of T-SQL

Column names are commonly used in order by clause of any select statement in T-SQL. For example a simple select statement in AdventureWorks database using order by clause is as follows.

 
USE AdventureWorks  
 GO  
 SELECT name, groupName, modifieddate  
 FROM humanresources.department  
 ORDER BY Groupname  
 GO  

Result is obviously three columns ordered by [GroupName]. Let us use another more simple approach while using order by clause. Instead of using column name, we may bring order to our results by using integer in order by clause. For example ordering our result set through integer, we have following way.

 
USE AdventureWorks  
 GO  
 SELECT name, groupName, modifieddate  
 FROM humanresources.department  
 ORDER BY 2  
 GO  

Result of both of our scripts is in same order. SQL Server starts count of columns used in select list by 1 and increments by 1. In case of above scripts used, as we have three columns in select list, so we may use integer value from 1 to 3 for first, second and third column respectively. Value provided may not be less than 1. And also it may not be greater than number of columns used in select list. As in our case maximum value is 3.
This syntax of order by using integers may be used in all versions of SQL Server. It is simple than using full column names. Changing the order of columns in select list will also change the criteria being used in order by clause.

Short Comings:

A short coming is there in this approach. By using integer in order by clause you can not order by any column that is not in select list. While by using column names in order list you can order even by the column that is not in select list.

Consult us to explore the Databases. Contact us