Temporal Record Selection - 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)

Temporal Record Selection

Author

Topic

golyath
Starting Member

21 Posts

Posted - 2009-02-21 : 11:19:47

I'm pretty sure there is a very simple answer to this but I cant get my head around it.

I have a table with the following columns:
id (int) PK
userId (int)
skillId (int)
date (datetime)
+ other columns that are not needed for description.

The records are temporal so any updates result in a new record being added with the current date/time in the date field. The id will be unique for every record.

I need to write a select statement to return the records for a certain user (e.g. userId) but that will return the latest record for each skillId.

So if a user has updated a certain skillId 3 times then there will be 3 records with that skillId but I need only the latest.

Thanks in advance.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-21 : 11:28:17

[code]Select UserID,SkillID,[Date]
from
(Select ROW_NUMBER() OVER(Partition by SkillID Order by [date] desc)
as seq,UserID,SkillID,[date] from Table)Z
Where Z.seq = 1[/code]

golyath
Starting Member

21 Posts

Posted - 2009-02-21 : 11:34:51

Thanks that works great!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-22 : 01:09:38

quote:

Originally posted by sodeep

Select UserID,SkillID,[Date]
from
(Select ROW_NUMBER() OVER(Partition by SkillID Order by [date] desc)
as seq,UserID,SkillID,[date] from Table)Z
Where Z.seq = 1

this will return the latest record for a skill but not for each user.I think OP is asking for latest skill record for each skill taken for a user. so shouldnt it be?


Select UserID,SkillID,[Date]
from
(Select ROW_NUMBER() OVER(Partition by UserID,SkillID Order by [date] desc)
as seq,UserID,SkillID,[date] from Table)Z
Where Z.seq = 1

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-22 : 08:32:11

quote:
Originally posted by visakh16

quote:
Originally posted by sodeep

Select UserID,SkillID,[Date]
from
(Select ROW_NUMBER() OVER(Partition by SkillID Order by [date] desc)
as seq,UserID,SkillID,[date] from Table)Z
Where Z.seq = 1

this will return the latest record for a skill but not for each user.I think OP is asking for latest skill record for each skill taken for a user. so shouldnt it be?

Select UserID,SkillID,[Date]
from
(Select ROW_NUMBER() OVER(Partition by UserID,SkillID Order by [date] desc)
as seq,UserID,SkillID,[date] from Table)Z
Where Z.seq = 1

I also thought same thing but later changed when he said:
"but that will return the latest record for each skillId.". Maybe you are right. If that is the case,this will be enough.

Select UserId,SkillID,MAX(Date)as MAXDATE
from table
Group by UserId,SkillID
Order by UserId,SkillID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources