how to obtain one event-row per user? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to obtain one event-row per user?

Author

Topic

sapeish
Starting Member

2 Posts

Posted - 2007-09-24 : 12:04:27

Hi there, I hope you can help me with this problem:
I have this Events table:
|UserId|eventTime|eventDoor|eventType|
-----------------------------------------

so what I need is to get the last registered event(usrId,eventTime,eventType) of each userId in a given eventDoor.
It looks like an easy query but for me it turned a nightmare.. the following query gives me what i want except that I cant get to filter by UserId so it shows onlly one registry per userId(the last one):

SELECT [UserId],[eventTime],[eventType]
FROM [Events]
WHERE [eventDoor]='1028'
order by [eventTime] DESC

any ideas???? using disctinct(UserId) wont work...

Sapeish

Kristen
Test

22859 Posts

Posted - 2007-09-24 : 12:13:49

This perhaps?


SELECT [UserId],[eventTime],[eventType]
FROM (
          SELECT userId, [MaxEventTime] = MAX(eventTime)
          FROM   [Events]
          GROUP BY userId 
     ) AS MaxE
     JOIN [Events] AS E
        ON E.userId = MaxE.userId
       AND E.eventTime = MaxE.MaxEventTime
WHERE [eventDoor]='1028'
order by [eventTime] DESC

Kristen

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-24 : 14:59:11

Mayby something like this?

SELECT
	UserID,
	EventTime,
	EventDoor,
	EventType
FROM
	(
		SELECT
			UserID,
			EventTime,
			EventDoor,
			EventType,
			ROW_NUMBER() OVER (PARTITION BY UserID, EventDoor ORDER BY EventTime DESC) AS RowNum
		FROM
			Events AS E
	) AS Temp
WHERE
	RowNum = 1
	AND EventDoor = 1028


-- OR 

SELECT
	UserID,
	EventTime,
	EventDoor,
	EventType
FROM
	(
		SELECT
			UserID,
			EventTime,
			EventDoor,
			EventType,
			ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY EventTime DESC) AS RowNum
		FROM
			Events AS E
		WHERE
			EventDoor = 1028
	) AS Temp
WHERE
	RowNum = 1

sapeish
Starting Member

2 Posts

Posted - 2007-09-24 : 17:10:12

Hi guys, thanks a lot...both solutions work fine, except that in Kristen solution there is an error:
The WHERE [eventDoor]='1028' statement must be within the nested select. This is because the MAX(eventTime) may be found in a different door than '1028' and consequently the join conditions (E.eventTime = MaxE.MaxEventTime) would lead to assigning to a registry the MAX from another door.

The fixed query is:

SELECT [UserId],[eventTime],[eventType]
FROM (
SELECT userId, [MaxEventTime] = MAX(eventTime)
FROM [Events]
WHERE [eventDoor]='1028'
GROUP BY userId
) AS MaxE
JOIN [Events] AS E
ON E.userId = MaxE.userId
AND E.eventTime = MaxE.MaxEventTime
order by [eventTime] DESC

Anyhow thanks so much!!!

sapeish.

Kristen
Test

22859 Posts

Posted - 2007-09-25 : 02:09:52

"The WHERE [eventDoor]='1028' statement must be within the nested select"

Good point, and it will mean that the nested select returns fewer rows, which will improve efficiency

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources