OrderBy clause sorting issue?

Question

I am trying to order by Date in SQL Server I am facing a weird issue. There are 2 cases I should explain to help you understand my issue .

Case 1 :

select [MonthName] 
from prod.[dim date] 
where F_Year = 2014   
order by [Date]

My output:

june june june july july july july august  
// Here I get duplicates but order by is working as expected

Case 2 : tried to remove duplicates by using "distinct"

select distinct [MonthName] 
from prod.[dim date] 
where F_Year = 2014   
order by [Date]

My output:

August july june 
// Order By not working as expected (ordering alphabetical wise ) .

Any workaround is appreciated

What's the **datatype** of `date` in your table? Can you show us the table structure (columns and their datatypes) and your data that's stored in that table? — marc_s, Jul 18 '14 at 06:02
How does `SELECT DISTINCT [MonthName] ... ORDER BY [Date]` even work? SQL Server won't let you order by fields that don't appear in the `SELECT DISTINCT` clause. — ta.speot.is, Jul 18 '14 at 06:04
Did you mean `order by [MonthName]` in the second statement? — dotnetom, Jul 18 '14 at 06:06
Making some assumptions about your data... `SELECT [MonthName] ... GROUP BY MonthName ORDER BY MIN([Date])` — Martin Smith, Jul 18 '14 at 06:06

score 0 · Answer 1 · edited May 23 '17 at 11:57

Firstly your query seems to be working off a table with 3 fields representing possibly a single date or maybe 2 - hard to tell without the structure but I can see : F_Year, [Date] & [MonthName]. So if these actually represent a single date, then revert to that single date and use formatting to determine F_Year and MonthName.

Secondly check out these posts for a probable answer to your question:

Sort by Date in SQL

Convert Month Number to Month Name Function in SQL

OrderBy clause sorting issue?

1 Answers1