Selecting specific Row_Number using Where clause? - SQL Server Forums

Home | Weblogs | Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Selecting specific Row_Number using Where clause?

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2012-02-03 : 16:51:41

I have an ad-hoc query like this:


Select Col1, Col2, Col3, Row_Number() Over(Order By Col1) RN
From Tbl1
Where Col2 != Col3

I'd like to loop through the results using a while loop, via T-SQL, but I'm running into a slight obstacle. I'd like to select a specific row. Now, I could make the above query a subquery and select from it, but I am concerned about performance issues and I would like to know if there is a preferred or better method.

This query will return an error but I hope it will clarify my desired result, thank you:


-- If this was allowed, it'd select 2nd row of data.
Select Col1, Col2, Col3, Row_Number() Over(Order By Col1) RN
From Tbl1
Where Col2 != Col3 and Row_Number() Over(Order By Col1) = 2

Thank you very much for any help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-03 : 17:00:11

use a derived table so you can use RN as a column.

No, you're never too old to Yak'n'Roll if you're too young to die.

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2012-02-03 : 17:06:36

Thank you for your help. Yes, I thought that was a good method. I'm a bit rusty with SQL 2008 and I thought that there may have been a better method than using a derived table. Thanks again for your help.

quote:
Originally posted by webfred

use a derived table so you can use RN as a column.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources