Click here to Skip to main content
12,395,871 members (60,991 online)
Rate this:
Please Sign up or sign in to vote.
Hi Everyone,
I need an answer to get nth records from the table in SQL Server. How to achieve this
Id        Name      Age
1        Ali        25
2        John       89
3        Mitchell   67
4        Azmaim     45
5        Shahid     46
6        Qureishi   32
7        Mayank     53
8        Agarwal    39
9        Morkel     33
10       Rajnikanth 42

I need a query to get the 5th record
Posted 4-Apr-13 4:21am
joshrduncan2012 4-Apr-13 10:30am
I suggest starting with reading the T-SQL MSDN documentation on SELECT statements.

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

The first thing you have to realise is that SQL is under no compunction to return rows in any particular order unless you give it an specific ORDER BY clause: A non-ordered SELECT statement is at liberty to return rows in any order SQL deems efficient, which may not be the same next week. So, first decide what you want to order by: in this case I assume it is the Id.
Try something like this:
WITH myTableWithRows AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY myTable.Id)) as row, *
    FROM myTable)
SELECT Id, [Name], Age FROM myTableWithRows WHERE row = 5
Maciej Los 4-Apr-13 11:39am

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web01 | 2.8.160721.1 | Last Updated 4 Apr 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100