Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
Hi,I am trying to select the Address data from the second most recent row of n number of rows for a given Login in the table Address. If I run
SELECT TOP 1 * FROM (SELECT TOP 2 * FROM Session WHERE Login = '9B1A3BA5-9AF1-4014-8A3C-348566861765' ORDER BY StartDateTime DESC) Q ORDER BY StartDateTime ASC
then I get back the row I want. However I can't seem to get just the Address data out of this as
SELECT Address FROM (SELECT TOP 1 * FROM (SELECT TOP 2 * FROM Session WHERE Login = '9B1A3BA5-9AF1-4014-8A3C-348566861765' ORDER BY StartDateTime DESC) Q ORDER BY StartDateTime ASC)
throws an error. I am sure it's something simple but I'd really appreciate any help in getting just the Address.Many thanks in advance for any responses
SELECT Address FROM (SELECT TOP 1 * FROM (SELECT TOP 2 * FROM Session WHERE Login = '9B1A3BA5-9AF1-4014-8A3C-348566861765' ORDER BY StartDateTime DESC) A Q ORDER BY StartDateTime ASC) B
or
SELECT TOP 1 Address FROM (SELECT TOP 2 * FROM Session WHERE Login = '9B1A3BA5-9AF1-4014-8A3C-348566861765' ORDER BY StartDateTime DESC) A Q ORDER BY StartDateTime ASC
CoreyI Has Returned!!
sz3y1w
Starting Member
5 Posts
Posted - 2011-05-16 : 17:00:44
Thanks for the quick reply guys - really appreciated.You were right SELECT Address FROM (SELECT TOP 1 * FROM (SELECT TOP 2 * FROM Session WHERE Login = '9B1A3BA5-9AF1-4014-8A3C-348566861765' ORDER BY StartDateTime DESC) A Q ORDER BY StartDateTime ASC) Bworked. Great stuff.I tried using ROW_NUMBER(), I am sure it's more efficent but I've never used it before (or even heard of it before). I'd like to use it if I can but I am not sure how to just return the Address without bringing back the ROW_NUMBER itself as well. This doesn't work but the nearest I've got is -SELECT Address FROM (SELECT Address, ROW_NUMBER() over (ORDER BY StartDateTime desc) AS row FROM Session WHERE Login = '9B1A3BA5-9AF1-4014-8A3C-348566861765')WHERE row = 2
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts
Posted - 2011-05-16 : 21:01:52
May be this is how you can have same result with Row_Number SELECT Address FROM ( SELECT Address,Rno= ROW_NUMBER() over (ORDER BY StartDateTime ASC) FROM (SELECT TOP 2 * FROM Session WHERE Login = '9B1A3BA5-9AF1-4014-8A3C-348566861765' ORDER BY StartDateTime DESC) A )B Where Rno=1CheersMIK
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts
Posted - 2011-05-17 : 08:28:59
Same issue possibly. What version of SQL are you using?
quote:Originally posted by sz3y1w Thanks for the quick reply guys - really appreciated.You were right SELECT Address FROM (SELECT TOP 1 * FROM (SELECT TOP 2 * FROM Session WHERE Login = '9B1A3BA5-9AF1-4014-8A3C-348566861765' ORDER BY StartDateTime DESC) A Q ORDER BY StartDateTime ASC) Bworked. Great stuff.I tried using ROW_NUMBER(), I am sure it's more efficent but I've never used it before (or even heard of it before). I'd like to use it if I can but I am not sure how to just return the Address without bringing back the ROW_NUMBER itself as well. This doesn't work but the nearest I've got is -SELECT Address FROM (SELECT Address, ROW_NUMBER() over (ORDER BY StartDateTime desc) AS row FROM Session WHERE Login = '9B1A3BA5-9AF1-4014-8A3C-348566861765') AWHERE row = 2
CoreyI Has Returned!!
sz3y1w
Starting Member
5 Posts
Posted - 2011-05-17 : 13:05:18
Using SQL Server 2005.SELECT AddressFROM (SELECT Address,Rno= ROW_NUMBER() over (ORDER BY StartDateTime ASC)FROM (SELECT TOP 2 * FROM Session WHERE Login = '9B1A3BA5-9AF1-4014-8A3C-348566861765' ORDER BY StartDateTime DESC) A)B Where Rno=1is also working great. I'll go with this I think.Thanks once again all for your quick responses and excellent suggestions.