latest status (select records with highest sequen

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 2008 Forums

Transact-SQL (2008)

latest status (select records with highest sequen

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-01-29 : 13:56:14

I have a huge table
this table is populated from information sent from another site every sec
the information of a user will be repeated several time sometimes no change other time have changes
information about users the latest information should be implemented
seqid specifies the order we receive the information the thing that will not change is the userid
seqid,userid,firstname,lastname,loginname,etc
I want to get the userid,and his latest information for all users

Thanks

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-29 : 14:12:19

[code]
SELECT seqid,userid,firstname,lastname,loginname,..
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY userid ORDER BY seqid DESC) AS Rn,*
FROM table
)t
WHERE Rn=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-01-29 : 16:36:48

Thanks so much

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-29 : 16:47:20

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-01-29 : 18:09:43

Is there a way to delete from the table all the records except the ones mentioned above

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-29 : 18:22:20

yes. just use below modified suggestion


DELETE t
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY userid ORDER BY seqid DESC) AS Rn
FROM table
)t
WHERE Rn>1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-01-29 : 20:51:01

Thank you

sarah

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources