Order By Clause and Literal Values in SQL Server

The literal value in an ORDER BY Clause can be an ordinal position of the column or just literal values depending on the usage

Ordinal position in a table refers to the position of the column in a table. It can also point to the column number in a SELECT statement

Consider the following set of data

create table testing(id int, names varchar(100))
insert into testing(id,names)
select 18,'Charles' union all
select 20,'Ashok' union all
select 19,'Birla' ;

The select statement

select * from testing order by 1

returns the following result



The number 1 in Order by clause refers to the first column listed in the SELECT statement so the result is ordered by column ID

The select statement

select * from testing order by 2

returns the following result


As you see the resultset is ordered by second column NAMES

But if you use a CASE expression and a number, it will behave differently. Suppose you want to keep the name Birla in first row and sort other rows by alphabetical order of remaining names, you can use the following statement

select * from testing order by case when names='Birla' then 1 else 2 end,names

The result is



In the above statement, number 1 and 2 are literal values and do not refer any columns. If the name is Birla, a value 1 is assigned otherwise 2 is assigned and ordered by these values first, then ordered by names.

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan