Select nth row of a table in sql server

Question

5.00/5 (1 vote)

See more:

SQL

server

Sir,
I want to select my nth row of my table.
like 3rd row,4th row.......

Posted 2-Jul-11 2:47am

manasBonton

Add a Solution

6 solutions

Solution 1

you can use row_number function. Refer to following MSDN link ROW_NUMBER (Transact-SQL)[^].

Posted 2-Jul-11 3:08am

Vivek Krishnamurthy

Solution 2

for example, to select 5th row

SELECT TOP 1 T.* FROM
(SELECT TOP 5 * FROM DBT_Person ORDER BY AGE ASC) AS T
ORDER BY T.AGE DESC;

Posted 2-Jul-11 3:21am

legendjslc

Solution 4

If you could, uses LINQ to SQL. That kind of stuff is much easier with Linq To SQL.

var result = (
  from m in dataContext.theTable
  select m).Skip(50).Take(1);

Posted 2-Jul-11 3:35am

Philippe Mori

Solution 5

Here you go...other alternative ways
How To Select nth Row From A Table In SQL Server[^]
SQL SERVER 2008 – How to select EVERY Nth row from a table[^]
How to select the nth row in a SQL database table?[^]

Posted 2-Jul-11 4:17am

thatraja

Solution 6

SQL

select * from(
select id,name,row_number() over(order by id) as 'row'  from student)as temp where row=n

Reference

Posted 15-Aug-12 21:40pm

balajikcp8

Updated 15-Aug-12 21:44pm

v2

Comments

Member 12635513 14-Jul-16 12:40pm

This solution helped me tremendously 2008 sql server. Top N wasn't working due to poor data quality. Thank you very much.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2011-07-02T03:26:00

Solution 3

Try:

WITH myTableWithRows AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY myTable.SomeField)) as row,*
    FROM myTable)
SELECT * FROM myTableWithRows WHERE row = 3

Posted 2-Jul-11 3:26am

OriginalGriff

Comments

Santhosh Kumar Jayaraman 16-Aug-12 3:49am

good one.