SQL Group By One Field and Order By Another Field

Question

The Table:

declare @Table table (    
 id int,    
 ticketid int,    
 sponsor int,    
 dev int,
 qa int,
 savedate datetime
)

insert into @Table values (1,100,22,0, 0, '2008-10-29 11:17:59.527')
insert into @Table values (2,100,5,0, 0, '2008-10-29 11:00:37.030')
insert into @Table values (3,101,22,0, 0, '2009-10-29 11:10:27.687')
insert into @Table values (5,101,44,0, 0, '2008-10-31 12:07:52.917')
insert into @Table values (6,101,32,0, 0, '2009-06-30 08:16:12.343')
insert into @Table values (7,101,44,0, 0, '2009-10-31 10:12:11.369')

I'm trying to select the top 1 max of savedate where recordid is a certain record, grouped by sponsor.

My progress:

select max(savedate)
from @Table
where ticketid = 101
group by sponsor

Returns

2009-10-29 11:10:27.687
2009-06-30 08:16:12.343
2009-10-31 10:12:11.370

Close, I'm grouped correctly but I want the top 1 most recent date. So I do this:

select top 1 max(savedate)
from @Table
where ticketid = 101
group by sponsor

Returns

2009-10-29 11:10:27.687

Woohoo, got it, time for a break.. wait.. that's not thie most recent date! Let's try to order these by savedate

select top 1 max(savedate)
from @Table
where ticketid = 101
group by sponsor
order by savedate desc

Oh no! The dreaded:

"Column "@Table.savedate" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause."

But savedate IS aggregated in the select list! How do I do what I want to do?

Answer 1

After typing this thing out and making sure I don't miss a detail, I came up with the answer right at the end. I figured I'd add it anyway so I or anyone else can find it later if they are as short sighted as I was in this instance.

select top 1 max(savedate) as date
from @Table
where ticketid = 101
group by sponsor
order by date desc

max(savedate) is not the same as savedate! Aliasing then refering to the aggregate worked perfectly:

2009-10-31 10:12:11.370

Hope this helps someone.

Answer 2

select top 1 max(savedate)
from @Table
where ticketid = 101
group by sponsor
order by max(savedate) desc

Answer 3

You probably wanted to return the sponsor along with the MAX(savedate).

If you didn't then note that this query:

SELECT  MAX(savedate)
FROM    @Table
WHERE   ticketid = 101

is completely identical to your solution

select top 1 max(savedate) as date
from @Table
where ticketid = 101
group by sponsor
order by date desc

, while being much more efficient.

Alternatively, you can just do the following:

SELECT  TOP 1 sponsor, savedate
FROM    @Table
WHERE   ticketid = 101
ORDER BY
        savedate DESC

Update:

This query will return the date when the task was last assigned to a current sponsor:

declare @Table table (    
 id int PRIMARY KEY,    
 ticketid int,    
 sponsor int,    
 dev int,
 qa int,
 savedate datetime
)

insert into @Table values (1,100,22,0, 0, '2008-10-29 11:17:59.527')
insert into @Table values (2,100,5,0, 0, '2008-10-29 11:00:37.030')
insert into @Table values (3,101,22,0, 0, '2009-10-29 11:10:27.687')
insert into @Table values (5,101,44,0, 0, '2008-10-31 12:07:52.917')
insert into @Table values (6,101,32,0, 0, '2009-06-30 08:16:12.343')
insert into @Table values (7,101,44,0, 0, '2009-10-30 10:12:11.369')
insert into @Table values (8,101,44,1, 0, '2009-10-31 10:12:11.369')

;WITH    rows AS
        (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY ticketid ORDER BY savedate DESC) AS rn
        FROM    @Table
        )
SELECT  rl.sponsor, ro.savedate
FROM    rows rl
CROSS APPLY
        (
        SELECT  TOP 1 rc.savedate
        FROM    rows rc
        JOIN    rows rn
        ON      rn.ticketid = rc.ticketid
                AND rn.rn = rc.rn + 1
                AND rn.sponsor <> rc.sponsor
        WHERE   rc.ticketid = rl.ticketid
        ORDER BY
                rc.rn
        ) ro
WHERE   rl.rn = 1

I added one more record for sponsor 44 to the test data.

The query will return the rows 1 and 7, since the sponsor did not change in row 8.