CODEWITHSUNDEEP
sqlsql-server
Kivan Ilangakoon
Kivan Ilangakoon

Reputation: 457

sorting month in ascending order

I have looked at related Qs on Stack. I am trying to sort my query to display month in ascending order. A similar post on Stack said that I must indicate "ASC", but this doesn't work for me. I have written an Order By. I cannot figure out why it is not working. 

SELECT  DATENAME( MONTH,(Submission.SubmissionDate)) AS [Date]        
FROM Submission 
GROUP BY DATENAME( MONTH,(Submission.SubmissionDate)) ORDER BY [Date] ASC;

This is the output: 

  Month
  August
 February
 September

Note: this is just a part of my query, I didnt think it would necessary to show the rest, which relates to other attributes from my table (Not month related)

Edit: This is my Entire Query: (The Initial One)

SELECT  DATENAME( MONTH,(Submission.SubmissionDate)) AS [Date], 
SUM( CASE WHEN Submission.Status='Under review' THEN 1 ELSE 0 END) [Under_Review], 
SUM( CASE WHEN Submission.Status='Accepted' THEN 1 ELSE 0 END) [Accepted], 
SUM( CASE WHEN Submission.Status='Rejected' THEN 1 ELSE 0 END) [Rejected], 
SUM( CASE WHEN Submission.Status='In print' THEN 1 ELSE 0 END) [In_print], 
SUM( CASE WHEN Submission.Status='Published' THEN 1 ELSE 0 END) [Published] 
FROM Submission INNER JOIN ((Faculty INNER JOIN School ON Faculty.FacultyID = School.[FacultyID]) INNER JOIN (Researcher INNER JOIN ResearcherSubmission ON Researcher.ResearcherID = ResearcherSubmission.ResearcherID) ON School.SchoolID = Researcher.SchoolID) ON Submission.SubmissionID = ResearcherSubmission.SubmissionID 
GROUP BY DATENAME( MONTH,(Submission.SubmissionDate)) 
ORDER BY DATENAME( MONTH,(Submission.SubmissionDate));

Upvotes: 1

Views: 1346

Answers (3)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

SELECT  DATENAME( MONTH, Submission.SubmissionDate) AS [Date]        
FROM Submission 
ORDER BY datepart(mm,Submission.SubmissionDate)

You don't need a group by (for the query shown). Also, when you order by month name it would return results in the alphabetical order of month name. You should not use previously defined aliases in the where,order by having and group by clauses.

Edit: The problem is with the join conditions. You should correct them as per the comments in line.

SELECT DATENAME( MONTH,(Submission.SubmissionDate)) AS [Date], 
SUM( CASE WHEN Submission.Status='Under review' THEN 1 ELSE 0 END) [Under_Review], 
SUM( CASE WHEN Submission.Status='Accepted' THEN 1 ELSE 0 END) [Accepted], 
SUM( CASE WHEN Submission.Status='Rejected' THEN 1 ELSE 0 END) [Rejected], 
SUM( CASE WHEN Submission.Status='In print' THEN 1 ELSE 0 END) [In_print], 
SUM( CASE WHEN Submission.Status='Published' THEN 1 ELSE 0 END) [Published] 
FROM Faculty 
INNER JOIN School ON Faculty.FacultyID = School.[FacultyID]
INNER JOIN Researcher ON School.SchoolID = Researcher.SchoolID
INNER JOIN ResearcherSubmission ON Researcher.ResearcherID = ResearcherSubmission.ResearcherID 
INNER JOIN SUBMISSION ON Submission.SubmissionID = ResearcherSubmission.SubmissionID 
GROUP BY DATENAME( MONTH,(Submission.SubmissionDate)) 
ORDER BY DATEPART( MONTH,(Submission.SubmissionDate))

Upvotes: 3

sstan
sstan

Reputation: 36473

A simple option is to add MONTH(Submission.SubmissionDate) to your group by clause, and order by that as well:

SELECT  DATENAME( MONTH,(Submission.SubmissionDate)) AS [Date]        
FROM Submission 
GROUP BY MONTH(Submission.SubmissionDate), DATENAME( MONTH,(Submission.SubmissionDate))
ORDER BY MONTH(Submission.SubmissionDate)

This will work for your real query as well.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can use MIN() or MAX():

SELECT  DATENAME(MONTH,(Submission.SubmissionDate)) AS [Date]        
FROM Submission 
GROUP BY DATENAME(MONTH,(Submission.SubmissionDate))
ORDER BY MIN([Date]) ASC;

This chooses an arbitrary date from each group and orders by that.

By the way, you probably should care about the year as well as the month. If so:

SELECT YEAR(s.SubmissionDate), DATENAME(MONTH, s.SubmissionDate) AS [Date]        
FROM Submission s
GROUP BY YEAR(s.SubmissionDate), DATENAME(MONTH, s.SubmissionDate)
ORDER BY MIN([Date]) ASC;

Upvotes: 0

Related Questions