SQL Server Portal

In my earlier article, I had discussed about how to sort month names in month order instead of alphabetical order . In that article, we had given the date and we had to convert into month and sort it. But, in today’s case we have month & year in the table and we need to sort it in month & year order instead of alphabetical order. To achieve this, we usually use lots of case statements and sort it.

Let me create an example to demonstrate it.

USE tempdb
GO
CREATE TABLE tbl_MonthName
(
[ID] INT,
[MONTH_NAME] VARCHAR(50),
[YEAR] VARCHAR(4)
)
GO

INSERT INTO tbl_MonthName
SELECT 1, N'January',2014 UNION ALL
SELECT 2, N'July',2013 UNION ALL
SELECT 3, N'February',2014 UNION ALL
SELECT 4, N'September',2013 UNION ALL
SELECT 5, N'March',2014 UNION ALL
SELECT 6, N'April',2014 UNION ALL
SELECT 7, N'November',2013 UNION ALL
SELECT 8, N'May',2014 UNION ALL
SELECT 9, N'June',2014 UNION ALL
SELECT 10, N'August',2013 UNION ALL
SELECT 11, N'October',2013 UNION ALL
SELECT 12, N'December',2013
GO

SELECT * FROM tbl_MonthName
GO
--OUTPUT

As you can see, the above result set is not sorted properly.

Given below is a simple solution to sort data in month and year order.

--This script is compatible with SQL Server 2005 and above
USE tempdb
GO
SELECT * FROM tbl_MonthName
ORDER BY [YEAR],MONTH('1' + [MONTH_NAME] +'00')
--OUTPUT