Sql-server – Select query – group by and sub group by

group byselectsql serversql-server-2008

CREATE TABLE [dbo].[temptable]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [uid] INT NOT NULL, 
    [pid] INT NOT NULL, 
    [event] INT NOT NULL, 
    [timeStamp] DateTime NOT NULL 
)

I want to select count of uid (group by pid) where event = @event AND timeStamp = [MAX of timestamp](group by uid).

Note that pid..uid is 1..many relationship

Id,uid,pid,event,timeStamp
1,20,300,100,'yesterday'
2,20,300,200,'today'
3,20,300,400,'daybefore'
4,30,300,100,'yesterday'
5,30,300,400,'daybefore'
6,40,300,100,'today'
7,40,300,200,'yesterday'
8,40,300,400,'daybefore'

For the above data, I should get below results if @event = 200

pid, count
300, 1

It shouldn't count uid 30 because there is no event 200 for uid 30. And it should also NOT count uid 40, because event for max(timestamp) i.e. today is 100 which doesn't match to @event i.e. 200.

TimeStamp is DateTime field but I've kept is relative in sample data for simplicity.

Best Answer

If I have figured correctly, what you are after:

; WITH cte AS
  ( SELECT pid, uid, event, timeStamp,
           maxts = MAX(timeStamp) OVER (PARTITION BY pid, uid)
    FROM tableX
  )
SELECT pid, cnt = COUNT(*)
FROM cte
WHERE maxts = timeStamp
  AND event = 200 
GROUP BY pid ;

If there is a chance that you have 2 rows with identical pid, uid and timestamp, you'll need to replace COUNT(*) with COUNT(DISTINCT uid).

Related Question