SQL Server - Data not in sequence query

Dear All,

Help me out the below query works fine about it did not get the data in a sequence month wise i used order by 1 or
 

ORDER BY CAST (DATEPART (YYYY,Otr.START_DATETIME) AS varchar(30))

+ CAST (DATEPART (mm,Otr.START_DATETIME) AS varchar(30)) ASC or DESC both

But it did not work for me. The query and results are below


SELECT

CAST (DATEPART (YYYY,Otr.START_DATETIME) AS varchar(30))

+ CAST (DATEPART (mm,Otr.START_DATETIME) AS varchar(30)),

ISNULL(SUM(Otr.[Column_1]),0),

( SELECT ISNULL(SUM(Inr.[Column_1]),0)

FROM Table1 Inr

WHERE Inr.START_DATETIME <= max(Otr.START_DATETIME)

) AS PREV_Test_1_Value

FROM table2 Otr

WHERE Otr.START_DATETIME <= '11/30/2011'

GROUP BY CAST (DATEPART (YYYY,Otr.START_DATETIME) AS varchar(30))

+ CAST (DATEPART (mm,Otr.START_DATETIME) AS varchar(30))

ORDER

Month	Test_1	PREV_Test_1_Value
201110	1811.748	6039.16
201111	1237.832	7276.992
20118	1811.748	1811.748
20119	2415.664	4227.412

BY 1

Thanks
hammeed

select cur.*
from orders cur
left join orders prev 
    on cur.webordernumber = prev.webordernumber + 1
    and cur.webstoreid = prev.webstoreid
where cur.webordernumber <> 1
and prev.webordernumer is null

Yes when you are doing ORDER by on Datetime after converting it to varchar than it sort based on left to right character and 0 to 9 digit based on varchar datatype where you need based on interger datatype
 eg  you get result as 201110
201111
20119

So when you want to sort as date time than you need to change you query as follows

SELECT CAST (DATEPART (YYYY,Otr.START_DATETIME) AS varchar(30))
+ CAST (DATEPART (mm,Otr.START_DATETIME) AS varchar(30)),
ISNULL(SUM(Otr.[Column_1]),0),
( SELECT ISNULL(SUM(Inr.[Column_1]),0) 
FROM Table1 Inr
WHERE Inr.START_DATETIME <= max(Otr.START_DATETIME)
) AS PREV_Test_1_Value
FROM table2 Otr WHERE Otr.START_DATETIME <= '11/30/2011'
GROUP BY CAST (DATEPART (YYYY,Otr.START_DATETIME) AS varchar(30))
+ CAST (DATEPART (mm,Otr.START_DATETIME) AS varchar(30)) 
ORDER BY DATEPART (YYYY,Otr.START_DATETIME)  ASC,  DATEPART (mm,Otr.START_DATETIME)  ASC

you need order by as follows then first it sort on yyyy and after that sort on mm based on interger because I am not converting it into varchar in roderby clause

This error is not due to Order by, this is due to Group by which need aggrigate function.
In fact, you need just remove group by only use order by because no need group by in your requirment if run below query with order by it self sort on first yyyy and then sort by mm within that year what you want.

SELECT CAST (DATEPART (YYYY,Otr.START_DATETIME) AS varchar(30))
+ CAST (DATEPART (mm,Otr.START_DATETIME) AS varchar(30)),
ISNULL(SUM(Otr.[Column_1]),0),
( SELECT ISNULL(SUM(Inr.[Column_1]),0) 
FROM Table1 Inr
WHERE Inr.START_DATETIME <= max(Otr.START_DATETIME)
) AS PREV_Test_1_Value
FROM table2 Otr WHERE Otr.START_DATETIME <= '11/30/2011'
BY DATEPART (YYYY,Otr.START_DATETIME)  ASC,  DATEPART (mm,Otr.START_DATETIME)  ASC

Thanks you so much for your help and kindness.


Regards,
hammeed