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
 General SQL Server Forums
 New to SQL Server Programming
 Help selecting data from 2nd row of a table

Author  Topic 

sz3y1w
Starting Member

5 Posts

Posted - 2011-05-16 : 16:00:42
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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-16 : 16:09:01
Can you use ROW_NUMBER() instead?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-05-16 : 16:09:39
What is the error?

maybe:

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



Corey

I Has Returned!!
Go to Top of Page

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) B

worked. 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
Go to Top of Page

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=1



Cheers
MIK
Go to Top of Page

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) B

worked. 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') A
WHERE row = 2



Corey

I Has Returned!!
Go to Top of Page

sz3y1w
Starting Member

5 Posts

Posted - 2011-05-17 : 13:05:18
Using SQL Server 2005.

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=1

is also working great. I'll go with this I think.

Thanks once again all for your quick responses and excellent suggestions.
Go to Top of Page
   

- Advertisement -