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.
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.
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
1,209 total views, 6 views today