Home » SQL Server ORDER BY

SQL Server ORDER BY

SQL ORDER BY keyword is used to sort the result set in ascending or descending order.


The ORDER BY keyword sorts the records in ascending order by default.

To sort the records in descending order, use the DESC keyword.

Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ...  ASC | DESC;

ASC is used to sort data in ascending order .

DESC is used to sort data in descending order.

Lets look at an example of ORDER BY in SQL Server.

First we Create a table named as Dept and then Insert some records in this table.

CREATE TABLE DEPT (
DEPT_ID INT NOT NULL, 
DEPT_NAME VARCHAR(100), 
DEPT_CODE VARCHAR(30))

INSERT INTO DEPT (DEPT_ID, DEPT_NAME, DEPT_CODE)
VALUES( 501, 'IT', 001),
(502, 'FINANCE', 002),
(503, 'HR', 003),
(504, 'MIS', 004),
(505, 'CALL CENTER', 005),
(506, 'SALES', 006)

Now we have a sample table named as DEPT in SQL Serever as given below .

To Sorting the department details in descending order based on DEPT_NAME

Following statement uses order by statement and sorts the records in descending order based on DEPT_NAME.

 SELECT * FROM DEPT ORDER BY DEPT_NAME DESC

To sorting the Department Details in ascending order based on DEPT_CODE

Following statement uses order by statement and sorts the records in ascending order based on DEPT_CODE.

SELECT * FROM DEPT ORDER BY DEPT_CODE ASC 

Order by with Aggregate Function

To sorting the Department details based on the Length of DEPT_NAME

Following statement uses the Order By statement and check the length of Dept_Name using  LEN Function, then sorts the Department details based on the highest dept_name length will be on the top in sorting order.

SELECT DEPT_ID, DEPT_NAME, DEPT_CODE, LEN(DEPT_NAME) AS NAME_LEN
FROM DEPT 
ORDER BY LEN(DEPT_NAME) DESC 

 

Custom Sorting with ORDER BY clause

You can also implement the custom sorting using a ORDER BY clause, you can read Custom Sorting using Order BY

 

How to implement custom Sorting in SQL Server

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.