–CREATE A table
CREATE TABLE TESTORBERBY
(

ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY NOT NULL,
NAME VARCHAR(100),
SALARY BIGINT

)
GO

–Insert some values in the table
INSERT INTO TESTORBERBY VALUES ( ‘Pawan’ , 100000 )
INSERT INTO TESTORBERBY VALUES ( ‘Kumar’ , 107800 )
INSERT INTO TESTORBERBY VALUES ( ‘Rahul’ , 1000 )
INSERT INTO TESTORBERBY VALUES ( ‘Raman’ , 17000 )
INSERT INTO TESTORBERBY VALUES ( ‘Vipul’ , 1080 )
INSERT INTO TESTORBERBY VALUES ( ‘Pinal’ , 10200 )
INSERT INTO TESTORBERBY VALUES ( ‘Sharlee’ , 70000 )
INSERT INTO TESTORBERBY VALUES ( ‘Deepa’ , 200000 )

–check values in the table

SELECT * FROM TESTORBERBY

Output
__________

1 Pawan 100000
2 Kumar 107800
3 Rahul 1000
4 Raman 17000
5 Vipul 1080
6 Pinal 10200
7 Sharlee 70000
8 Deepa 200000

–try out some order by clause
SELECT * FROM TESTORBERBY ORDER BY SALARY ASC
SELECT * FROM TESTORBERBY ORDER BY SALARY DESC

SELECT * FROM TESTORBERBY ORDER BY [NAME] ASC
SELECT * FROM TESTORBERBY ORDER BY [NAME] DESC

–Use the below query to use case with Order by clause
DECLARE @Type INT
SET @Type = 2
SELECT *
FROM TESTORBERBY
ORDER BY
(
CASE
WHEN @TYPE = 1 THEN [NAME]
WHEN @TYPE = 2 THEN SALARY
END
) DESC

Output
_______________________

ID NAME SALARY
———– ——————-
8 Deepa 200000
2 Kumar 107800
1 Pawan 100000
7 Sharlee 70000
4 Raman 17000
6 Pinal 10200
5 Vipul 1080
3 Rahul 1000

(8 row(s) affected)

Pawan Kumar

Pawankkmr@hotmail.com

Advertisements