CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Pankaj Karmakar
Pankaj Karmakar

Reputation: 89

In Sql Server 2008 Order by clause is not working

I have run a query

SELECT Datename(MM, CONVERT(DATE, created_date))           AS open_date, 
       Count(Datename(MONTH, CONVERT(DATE, created_date))) AS created_request 
FROM   usm_request 
WHERE  Datename(YEAR, CONVERT(DATE, created_date)) = Datename(YEAR, Getdate()) 
GROUP  BY Datename(MM, CONVERT(DATE, created_date)) 
ORDER  BY Datename(MM, CONVERT(DATE, created_date)) ASC

and got the result

 open_date  created_request

   April          4
   February      194
   January       540
   March         186

But we need result as

  open_date  created_request

   January       540
   February      194
   March         186    
   April          4

Please help me.

Regards

Pankaj

Upvotes: 0

Views: 58

Answers (3)

Tim Schmelter
Tim Schmelter

Reputation: 460038

DATENAME returns a string not a datetime, so you get a lexicographic ordering.

You could use:

SELECT Datename(MM, CONVERT(DATE, created_date))           AS open_date, 
       Count(Datename(MONTH, CONVERT(DATE, created_date))) AS created_request 
FROM   usm_request 
WHERE  Datename(YEAR, CONVERT(DATE, created_date)) = Datename(YEAR, Getdate()) 
GROUP  BY  DATENAME(mm,created_date),
       DATEPART(yy, created_date), 
       DATEPART(mm, created_date)
ORDER  BY DATEPART(yy, created_date),
          DATEPART(mm, created_date)

... which orders by year + month (as int). You also have to include them in the GROUP BY.

However, why do you convert the datetime column always to Date? That seems to be redundant with methods like DATENAME. So i've omitted it in the GROUP BY and ORDER BY.

Upvotes: 2

Cristian Necula
Cristian Necula

Reputation: 1245

Your ORDER BY clause is receiving a string: the name of the month. This is why the rows are ordered alphabetically.

Try ordering by the actual date representation:

ORDER BY CONVERT(DATE, created_date) ASC

Upvotes: 1

koushik veldanda
koushik veldanda

Reputation: 1107

SELECT * FROM(
SELECT Datename(MM, CONVERT(DATE, created_date))           AS open_date, 
       Count(Datename(MONTH, CONVERT(DATE, created_date))) AS created_request 
FROM   usm_request 
WHERE  Datename(YEAR, CONVERT(DATE, created_date)) = Datename(YEAR, Getdate()) 
GROUP  BY Datename(MM, CONVERT(DATE, created_date)) 
) a
ORDER  BY a.created_request desc

Upvotes: -1

Related Questions