Unanswered: SQL Server 7 - need first() last() equivalent
This is making my head explode, so I thought I'd see if anyone here can help out.
I've made several attempts to do this in a SQL statement, but all have failed. I can do it correctly in a script, but thats not what I want to use.
I have a table with the following columns:
id - primary key of course
userid - numeric userid that corresponds with our users table
eventdate - the unix timestamp of when the even occurred
logintype - 1 for login, 2 for logout
I want to write a query that will give me the last action of every user. So it will dump out a dataset of distinct userid's with the eventdate and logintype, essentially telling me who is logged in, if their last even was a login.
Write a query using MAX(eventdate) grouped by the user. Then join this to the original table in your final view. It should work fine.
I'm sure clever folks could do it in one view but the effect will be the same.