Get latest entry per userid - SQL Server Forums

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)

Get latest entry per userid

Author

Topic

aoemraw
Starting Member

3 Posts

Posted - 2008-04-08 : 11:09:16

Hi,

I have the following situation:
I have a table that is log of transactions of useractions. Each time a userstatus changes, a new entry is made into the table.

The table has (currently) about 4 million records, and keeps growing each day with thousands of records. It logs the status of about 150 users (currently active).

What I now want is to get the log-entry from all the distinct users that have a log-entry on the latest day (so current day, 'where TimeStamp > 2008-04-08').

(extremely simplified) Example:
(date is y/m/d)


USERID     TIMESTAMP               STATUS
----------------------------------------------------
... (thousands and millions of records above)
Ed         2008-04-07 18:00:00     Logged_Out
Jim        2008-04-07 18:30:00     Blabla_Status_5
Jack       2008-04-07 19:00:00     Logged_Out
Jim        2008-04-07 19:30:00     Logged_Out
Jim        2008-04-08 06:00:00     Logged_In      (<< notice new day)
Jim        2008-04-08 06:01:00     Blabla-Status_1
Bob        2008-04-08 06:03:00     Logged_In
Fred       2008-04-08 06:05:00     Logged_In
Jim        2008-04-08 06:08:00     Blabla-Status_2
Jack       2008-04-08 06:12:00     Logged_In
Fred       2008-04-08 06:20:00     Blabla_Status_5
Jack       2008-04-08 06:12:00     Logged_Out
Fred       2008-04-08 06:12:00     Blabla_Status_4
Jack       2008-04-08 06:12:00     Logged_In
Jack       2008-04-08 06:12:00     Blabla_Status_7
----------------------------------------------------

The result should be:


USERID     TIMESTAMP               STATUS
----------------------------------------------------
Jack       2008-04-08 06:12:00     Blabla_Status_7
Fred       2008-04-08 06:12:00     Blabla_Status_4
Jim        2008-04-08 06:08:00     Blabla-Status_2
Bob        2008-04-08 06:03:00     Logged_In
----------------------------------------------------

(How) can this be done in one stored procedure?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 11:13:14

One stored procedure, or one query?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 11:14:20

SELECT UserID, Timestamp, Status
FROM (
SELECT UserID, Timestamp, Status, ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY Timestamp DESC) AS RecID FROM Table WHERE Timestamp >= '20080408'
) AS d
WHERE RecID = 1

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-08 : 11:15:32

[code]Select t1.UserID, t1.TimeStamp, t1.Status
from
Table t1 join
(Select UserID, max(TimeStamp) as TimeStamp
from table
where timestamp > '20080407') t2
on t1.UserID = t2.UserID and t1.TimeStamp = t2.TimeStamp[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

aoemraw
Starting Member

3 Posts

Posted - 2008-04-08 : 11:19:21

quote:
Originally posted by Peso

One stored procedure, or one query?

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-08 : 11:32:07

Please note that you should have a sql 2005 server with db compatibility level set to 90 to use ROW_NUMBER() function.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-09 : 03:08:08

And see what you can do with ROW_NUMBER() function
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

aoemraw
Starting Member

3 Posts

Posted - 2008-04-09 : 03:24:02

Thank you for all your input.

I've got it working now :)

I'm new to SQL, but after I saw the code you guys provided, I was like: duhhh... Why didn't I think of that?

I guess it's lack of experience. Need to practice more (especially with joins).

Anyway, many thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources