T-SQL: sorting results by year, month

问题:

I have the following statement:

select
    (datename(MM, SomeDate) + ' ' + datename(day, SomeDate)) as D,
    count(SomeID) as IDs
from dbo.SomeTable
where
    datename(MM, SomeDate) = @Month
    and datename(YYYY, SomeDate) = @Year
group by
    datename(YYYY, SomeDate),
    datename(MM, SomeDate),
    datename(DD, SomeDate);

It works but it sorts the result like this:

July 1, 2011 - 2
July 10, 2011 - 4
July 2, 2011 - 10

How do I make it to display July 10 after July 2? I know it's something simple but I can't find the problem. Thanks for any help!

EDIT: Guys, I know there must be an "order by", I thought it's quite obvious. But the "normal" ordering produces the same result (see my comments). I think it's related to collation but I'm not an expert there.

回答1:

You are losing information from somedate and it isn't available for ORDER BY anyway

Sorting by DATENAME isn't what you want because July is after August

So, add more derived columns to the GROUP BY to sort on later that use DATEPART which gives numbers

select
    (datename(MM, SomeDate) + ' ' + datename(day, SomeDate)) as D,
    count(SomeID) as IDs
from
    dbo.SomeTable
where
    datename(MM, SomeDate) = @Month
    and datename(YYYY, SomeDate) = @Year
group by
    datename(YYYY, SomeDate),
    datename(MM, SomeDate),
    datename(DD, SomeDate),
    datepart(YYYY, SomeDate),
    datepart(MM, SomeDate),
    datepart(DD, SomeDate)
order by
    datepart(YYYY, SomeDate),
    datepart(MM, SomeDate),
    datepart(DD, SomeDate)

回答2:

Managed to fix it by moving the body of your query into a subquery, including SomeDate in the group by clause (having ensured to remove the time portion).

I'd personally avoid using DateName for this query - I'd rather use DatePart and keep everything numeric, but based on your query:

Setup:

create table SomeTable (
    SomeDate datetime not null,
    SomeID int not null
)
go
insert into SomeTable (SomeDate,SomeID)
select '20110701',1 union all
select '20110702',1 union all
select '20110710',1

Query:

declare @Month varchar(10)
declare @Year int
select @Month='July',@Year=2011

select D,IDs from (
select
    (datename(MM, SomeDate) + ' ' + datename(day, SomeDate)) as D,
    count(SomeID) as IDs
    ,DATEADD(day,DATEDIFF(day,0,SomeDate),0) as SomeDate
from dbo.SomeTable
where
    datename(MM, SomeDate) = @Month
    and datename(YYYY, SomeDate) = @Year
group by
    datename(YYYY, SomeDate),
    datename(MM, SomeDate),
    datename(DD, SomeDate),
    DATEADD(day,DATEDIFF(day,0,SomeDate),0)
) t
order by SomeDate

回答3:

If SomeDate is of type DATE then you can simply add ORDER BY SomeDate. The GROUP BY can be simplified as well:

select
    (datename(MM, SomeDate) + ' ' + datename(day, SomeDate)) as D,
    count(SomeID) as IDs
from dbo.SomeTable
where
    datename(MM, SomeDate) = @Month
    and datename(YYYY, SomeDate) = @Year
group by
    SomeDate 
order by
    SomeDate ;

The WHERE can probably be simplified too.

---

If it's of type DATETIME, you can use:

select
    (datename(MM, DATEADD(dd, 0, DATEDIFF(dd, 0, SomeDate)))
      + ' ' + datename(day, DATEADD(dd, 0, DATEDIFF(dd, 0, SomeDate)))) as D,
    count(SomeID) as IDs
from dbo.SomeTable
where
    datename(MM, SomeDate) = @Month
    and datename(YYYY, SomeDate) = @Year
group by
    DATEADD(dd, 0, DATEDIFF(dd, 0, SomeDate)) 
order by
    DATEADD(dd, 0, DATEDIFF(dd, 0, SomeDate)) ;

回答4:

You need to add an ORDER BY clause and order by SomeDate.

回答5:

SELECT COUNT(*)
   /*Event_type
   EventDate*/ AS EventCount
  ,MONTH(EventDate) AS MONTH
  ,YEAR(EventDate) AS YEAR
  ,DATENAME(MONTH ,EventDate) AS MonthName
FROM   EventTable
WHERE  (Event_type = 'TypeOfEvent')
   AND (Userid NOT LIKE '%*%')
GROUP BY
   YEAR(EventDate)
  ,MONTH(EventDate)
  ,DATENAME(MONTH ,EventDate)
ORDER BY
   YEAR
  ,MONTH