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.
| Author |
Topic |
|
CoderLaura
Starting Member
2 Posts |
 Posted - 2009-02-18 : 15:21:08
|
| It seems like there should be an easy way to query this information but I couldn't think of one...I have a table called Users with columns like UserID (primary key), Name, Etc. I have another table called UserActions with columns like UserID, ActionID, ActionTime, etc. There can be many records for each UserID. I want to have a view that pulls the most recent action for each user from the UserAction table. So if there are 100 users, the view (or query) would list each user with its most recent action. Any suggestions? Thanks in advance. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-18 : 15:33:58
|
| [code]Select UserID,ActionID,ActionTime ....From(Select ROW_NUMBER() OVER(Partition by UserID Order by ActionID Desc)as RowID,* from UserAction)ZWhere Z.RowID = 1[/code] |
 |
|
|
SQLforGirls
Starting Member
48 Posts |
Posted - 2009-02-18 : 15:59:45
|
| Here's another way, just for fun. Might be a little easier to navigate when you go to join to the Users table for Name, etc.select UserID, ActionID, ActionTimefrom UserActions awhere a.ActionTime = (select max(ActionTime) from UserActions i where i.UserID = a.UserID) |
|
|
|
CoderLaura
Starting Member
2 Posts |
Posted - 2009-02-18 : 17:30:40
|
| Both solutions worked! Thanks for your help :-) |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-18 : 18:47:28
|
Good  |
|
|
|
|
|
|
|
|
Get top records query