SQL ORDER BY Clause

ORDER BY clause is used to sort the data in ascending or descending order, in SQL Server by default data will not be inserted in any order even if you have an index.

Syntax:

SELECT expressions
FROM tables
WHERE conditions
ORDER BY expression [ ASC | DESC ];

We will take an example of table [Person] to sort the column order by FirstName in ascending order.

SELECT
      [FirstName]
     ,[MiddleName]
     ,[LastName]
FROM [Person].[Person]
ORDER BY FirstName;

If you do not specify the keyword ASC, sorting by default will be in ascending order, this is equivalent of below query.

SELECT
      [FirstName]
     ,[MiddleName]
     ,[LastName]
FROM [Person].[Person]
ORDER BY FirstName ASC;

If you want to order the data in descending order then you have to specify ORDER BY DESC as shown below.

SELECT
      [FirstName]
     ,[MiddleName]
     ,[LastName]
FROM [Person].[Person]
ORDER BY FirstName DESC;

Sorting data using the numerical position of a column name

Instead of column name you can also specify numeric position of column to sort data as shown below.

SELECT
      [FirstName]
     ,[MiddleName]
     ,[LastName]
FROM [Person].[Person]
ORDER BY 1 ASC;

Note

It is considered bad practice to use numerical position instead of column names. For example, if you change the positions of columns, or if you add columns then it will give error.

Using  more than one column to sort data
Here we are sorting two columns FirstName and LastName.

SELECT
       [FirstName]
      ,[MiddleName]
      ,[LastName]
FROM [Person].[Person]
ORDER BY FirstName, LastName;

Combining Ascending and Descending order

With order by clause we can  combine the ASC and DESC keywords in a single statement, here is an example:

SELECT
       [FirstName]
      ,[MiddleName]
      ,[LastName]
FROM [Person].[Person]
ORDER BY FirstName ASC, LastName DESC;

Sorting Data using Column Alias

The use of an alias is allowed because the order by clause is last in logical query processing order, here is an example. [Refer SELECT Statement]

SELECT
      [FirstName]     
     ,[MiddleName]
     ,[LastName]
     ,[FirstName]+' '+[LastName] AS [FullName]
FROM [Person].[Person]
ORDER BY [FullName];

IIF() Logical Function in SQL Server

IIF() logical function has been introduced in SQL Server 2012. We already know about CASE expression and IF-Else statement in SQL Server so now you can also use IIF() function to get the same output in a short way. IIF() function’s execution plan is similar to CASE expression.

IIF() function returns one correct value based on true or false boolean expression values.

Syntax:

IIF ( boolean_expression, first_value, second_value )

IIF() function takes three arguments, first is boolean expression. When boolean expression evaluates to true then first value is the output and when boolean expression evaluates to false then second value is the output. Out of two values, highest precedence is checked and it returns the data type. We will see how to use IIF() function with examples.

Example 1:

SELECT IIF(99 > 100, 'TRUE', 'FALSE') AS OUTPUT 
GO

Here 99 is not greater than 100 so it will return second value as output which is FALSE.

Example 2:

DECLARE @STUDENT VARCHAR(100) = 'Siddiqui'
SELECT IIF(@STUDENT IN ('Manzoor', 'Siddiqui'), 'DBA', 'TechWriter') AS OUTPUT
GO

Here we are assigning and comparing string value and as assigned string value results in true so first value ‘DBA’ is the output.

Example 3:

DECLARE @gender1 VARCHAR(20) = 'M', @gender2 VARCHAR(20) = 'F', @gender3 VARCHAR(20) = 'X'
SELECT IIF(@gender1 = @gender2, 'MALE', IIF(@gender2 = @gender3, 'FEMALE', 'X')) AS Gender
GO

Here we are checking nested IIF() function to find the gender, it will return ‘X ‘as output.

Example 4:

DECLARE @valueX INT = 99, @valueY INT = 100;
SELECT IIF ( @valueX < @valueY, 'TRUE', 'FALSE') AS Output
GO

Here integer value X is less than value Y so it will return TRUE.