need help to get lastLoginDte - 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)

need help to get lastLoginDte

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2012-01-19 : 21:23:57

My table and data as following,


declare @tLogin table
(idx int identity, myID varchar(100), loginDte datetime, dtVersion timestamp)

insert into @tLogin(myID,loginDte) values('superadmin', '2012-01-01')
insert into @tLogin(myID,loginDte) values('superadmin', '2012-01-01')
insert into @tLogin(myID,loginDte) values('superadmin', '2012-01-07')
insert into @tLogin(myID,loginDte) values('superadmin', '2012-01-09')

I need to get lastLoginDte, which is
1. If exist 1 data for superadmin in the @tLogin, it'll will return no data for the lastLoginDte
2. If exist 2 or more data for superadmin in the @tLogin, it'll will return the 2nd row for the lastLoginDte

So far, my query as following,


select * from @tLogin where myID='superadmin'
order by dtVersion DESC

need help

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-20 : 06:47:23

[code]SELECT * FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY myId ORDER BY loginDte DESC) AS RN
FROM @tLogin
) S
WHERE RN = 2
AND myID='superadmin'[/code] If you have only one user

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2012-01-20 : 09:55:11

tq sunitabeck

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-20 : 10:15:29

You are very welcome.

And, in my earlier reply, when I trailed off with the sentence "If you have only one user..." what I meant to say was that "If you have only one user you would not need the partition by clause in the row_number function.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources