Auto order in selected

Question

I have a procedure that returns a daily report between an example time (CreateDate BETWEEN '2012/1/21' AND '2012/2/19')

I want this result

1/21
1/22
1/23
.
2/1
.
.

But the result is sorted automatically depending on the day! As below:

2/1
2/2
.
2/15
1/21
1/22
1/23
.
.

I cannot use order by (createdate) because I have this choice:

SELECT     COUNT(UserId) AS c, DAY(CreateDate) AS d
FROM       dbo.aspnet_Membership
WHERE     (CreateDate BETWEEN '2012/1/21' AND '2012/2/19')
GROUP BY DAY(CreateDate)

Answer 1

If you want to sort by month and day, you need a month, of course.

And you always need ORDER BY to guarantee the order of the result.

SELECT     COUNT(UserId) AS c, MONTH(CreateDate) AS m, DAY(CreateDate) AS d
FROM       dbo.aspnet_Membership
WHERE     (CreateDate BETWEEN '2012/1/21' AND '2012/2/19')
GROUP BY  MONTH(CreateDate) AS m, DAY(CreateDate) AS d
ORDER BY  MONTH(CreateDate) AS m, DAY(CreateDate) AS d

In this case, the order you received was a coincidence due to GROUP BY (which implies ORDER BY in MySQL, but not in other DBMSs)

Answer 2

If you mean that you want to ORDER in the afternoon,

it might work for you

SELECT  COUNT(UserId) AS c, DAY(CreateDate) AS d 
  FROM    dbo.aspnet_Membership 
WHERE   (CreateDate BETWEEN '2012/1/1' AND '2012/2/29')
  GROUP BY DAY(CreateDate) 
ORDER By d ASC