SQL Server – ORDER BY Clause

In this article, we are going to discuss the SQL order by clause and its uses

Order by clause we use to sorting of the records, The following illustrates the ORDER BY clause syntax:

SELECT
    select_list
FROM table_name
ORDER BY
    column_names

In the above syntax, you need to mention the column name which you want to sort, if you mentioned the multiple columns, the result set is sorted by the first column then that result set is sorted by the second column.

Default columns are sorted by ascending order, but through asc|desc the keyword, we can sort columns in any order, and null records placing in the lower order.

SQL Server ORDER BY clause example

We will use a student the table in the demonstration

Sort a result set by one column in ascending order

The below example will sort the result set by the Student_namein ascending order,

select Student_name,Mobile from 
dbo.student
order by Student_name

default record getting sorted by ascending order, the result of the above query is

Sort a result set by one column in descending order

The below example will sort the result set by the Student_name in descendingorder, you need to use desckeyword explicitly to sort in descendingorder,

select Student_name,Mobile from 
dbo.student
order by Student_name desc

the result set of the above example is

Sort a result set by a column that is not in the select list

You can sort the result set by column not present in the select statement, but we can’t sort the records by the column which is not present in the table.

select Email from 
dbo.student
order by Student_name desc

Output

Sort by ordinal positions of columns.

We can sort the column by its position in the select statement, if in the select statement all(*) is mentioned they it will take the first column from the table

select Student_name,Mobile 
from 
dbo.student
order by 1 desc

Output

select *
from 
dbo.student
order by 1 desc

Output

Sort a result set by multiple columns

In the below example, first, fetch the Student_name, Mobile from the table, and first records sort by Student_name then that result set to sort by Mobile column,

select Student_name,Mobile from 
dbo.student
order by Student_name,Mobile

result of the above query is

Sort a result set by multiple columns and different orders

In the below example, sorted the records by desc and asc order, the first result set sorted by the descending order and that result set again to sort by ascending order

select Student_name,Mobile from 
dbo.student
order by Student_name desc,Mobile asc

Output:

Sort the records by using expression

In this example, we can use the LEN() function to sort the records,

LEN()- Used to calculate the length of the string

select Student_name from 
dbo.student
order by LEN(Student_name) 

Output:

Need help?

Read this post again, if you have any confusion or else add your questions in Community