Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.