By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,266 Members | 1,297 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,266 IT Pros & Developers. It's quick & easy.

how to display nth record from table

P: 5
how to display nth record from table
Dec 20 '09 #1
Share this Question
Share on Google+
5 Replies


nbiswas
100+
P: 149
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
Expert 100+
P: 1,134
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
Expert 100+
P: 1,134
nbiswas posted while I was writing mine
Oh well you now have plenty of options to choose from
:)
Dec 21 '09 #4

ssnaik84
100+
P: 149
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
Expert 100+
P: 1,134
oops
good spot ssnaik84
Dec 23 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.