SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help selecting data from 2nd row of a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sz3y1w
Starting Member

United Kingdom
5 Posts

Posted - 05/16/2011 :  16:00:42  Show Profile  Reply with Quote
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

USA
36997 Posts

Posted - 05/16/2011 :  16:09:01  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 05/16/2011 :  16:09:39  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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

United Kingdom
5 Posts

Posted - 05/16/2011 :  17:00:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 05/16/2011 :  21:01:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 05/17/2011 :  08:28:59  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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

United Kingdom
5 Posts

Posted - 05/17/2011 :  13:05:18  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000