472,125 Members | 1,433 Online

# how to display nth record from table

how to display nth record from table
Dec 20 '09 #1
5 4136
nbiswas
149 100+
Try this (Suppose I want to find the 6th record among 10th record)

Sample data

Expand|Select|Wrap|Line Numbers
1. declare @t table(id int identity, name varchar(10))
2. insert into @t
3.     select 'name1' union all select 'name2' union all select 'name3' union all
4.     select 'name4' union all select 'name5' union all select 'name6' union all
5.     select 'name7' union all select 'name8' union all select 'name9' union all
6.     select 'name10'

Solution 1:(SQL SERVER 2005+)

Expand|Select|Wrap|Line Numbers
1. select x.id,x.name from (
2. select row_number()over(order by id) rn,t.* from @t t) x
3. where x.rn = 6
Solution 2:(SQL SERVER 6.5 +)

Expand|Select|Wrap|Line Numbers
1. select x.id,x.name from (
2. select id,name ,(select COUNT(*) from @t t2 where t2.id <=t1.id) as rn
3. from @t t1)x where x.rn = 6
4.
Output:

Expand|Select|Wrap|Line Numbers
1. id    name
2. 6    name6
Solution 3: With the help of a number table(SQL SERVER 6.5 +)

First create a number table

Expand|Select|Wrap|Line Numbers
1. declare @numbertab table(rownums int)
2. insert into @numbertab
3. select distinct column_id  from master.sys.all_columns order by 1 asc
Then fire the query

Expand|Select|Wrap|Line Numbers
1. select id, name
2. from @numbertab n join @t t
3. on n.rownums = t.id
4. where n.rownums = 6
Hope this helps
Dec 21 '09 #2
Delerna
1,134 Expert 1GB
There is no
Expand|Select|Wrap|Line Numbers
1. select nth record from table
2.
You will have to think of a way to simulate it.
How you do that depends on a lot of things.
1) What version of SQL server do you have?
SQL server 2005 has a function that adds a row number field to a query.
ealier versions can do that through an auto number field in a temp table

2) Does any of your data naturally contain something you can use for
record sequencing.
For example a date field...like this to get the 10th record
Expand|Select|Wrap|Line Numbers
1. SELECT Top 1 Date,Other,Fields
2. FROM
3. (    SELECT Top 10 Date,Other,Fields
4.      FROM TheTable
5. )a
6. ORDER BY Date DESC
7.
Dec 21 '09 #3
Delerna
1,134 Expert 1GB
nbiswas posted while I was writing mine
Oh well you now have plenty of options to choose from
:)
Dec 21 '09 #4
ssnaik84
149 100+
Expand|Select|Wrap|Line Numbers
1.  SELECT Top 1 Date,Other,Fields
2.  FROM
3.  (    SELECT Top 10 Date,Other,Fields
4.      FROM TheTable
5. )a
6.  ORDER BY Date DESC
Expand|Select|Wrap|Line Numbers
1.  SELECT Top 1 Date,Other,Fields
2.  FROM
3.  (    SELECT Top 10 Date,Other,Fields
4.      FROM TheTable
5.      ORDER BY Date DESC
6. )a
7.
this is probably the best solution for such a most commonly asked interview question ;)
Dec 22 '09 #5
Delerna
1,134 Expert 1GB
oops
good spot ssnaik84
Dec 23 '09 #6