Arrange the record in descending order of Month and Year

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 433
Suppose we have the below

SlNo	MonthName
1 FEB 2017
2 SEP 2014
3 MAY 2016
4 MAY 2015
5 MAY 2014
6 DEC 2016
7 DEC 2015
8 DEC 2014
9 FEB 2014
10 JAN 2017


The output should be

SlNo	MonthName
1 JAN 2017
2 FEB 2017
3 MAY 2016
4 DEC 2016
5 MAY 2015
6 DEC 2015
7 FEB 2014
8 MAY 2014
9 SEP 2014
10 DEC 2014


i.e. records are arranged in descending order of Month and Year

The below TSQL script will do so

DECLARE @T TABLE(SlNo INT IDENTITY,MonthName VARCHAR(20))
INSERT INTO @T VALUES
('FEB 2017'),('SEP 2014'),('MAY 2016'),('MAY 2015'),('MAY 2014')
,('DEC 2016'),('DEC 2015'),('DEC 2014'),('FEB 2014'),('JAN 2017')

--SELECT *
--FROM @T


SELECT
SlNo = ROW_NUMBER() OVER(ORDER BY YEAR(MonthName) DESC, MONTH(MonthName))
,MonthName
FROM @T


The Row_Number() function is generating the sequences based on the records sorted by Year in descending order followed by Month in Ascending order.

Comments or Responses

Login to post response