how to display nth record from table

[saga git]

how to display nth record from table

[nbiswas]

Try this (Suppose I want to find the 6th record among 10th record)

Sample data

declare @t table(id int identity, name varchar(10))
insert into @t 
    select 'name1' union all select 'name2' union all select 'name3' union all
    select 'name4' union all select 'name5' union all select 'name6' union all
    select 'name7' union all select 'name8' union all select 'name9' union all
    select 'name10'

Solution 1:(SQL SERVER 2005+)

select x.id,x.name from (
select row_number()over(order by id) rn,t.* from @t t) x
where x.rn = 6

Solution 2:(SQL SERVER 6.5 +)

select x.id,x.name from (
select id,name ,(select COUNT(*) from @t t2 where t2.id <=t1.id) as rn
from @t t1)x where x.rn = 6
 

Output:

id    name
6    name6

Solution 3: With the help of a number table(SQL SERVER 6.5 +)

First create a number table

declare @numbertab table(rownums int)
insert into @numbertab
select distinct column_id  from master.sys.all_columns order by 1 asc

Then fire the query

select id, name
from @numbertab n join @t t
on n.rownums = t.id
where n.rownums = 6

Hope this helps

[Delerna]

There is no

select nth record from table
 

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

SELECT Top 1 Date,Other,Fields 
FROM
(    SELECT Top 10 Date,Other,Fields 
     FROM TheTable
)a
ORDER BY Date DESC
 

[Delerna]

nbiswas posted while I was writing mine
Oh well you now have plenty of options to choose from
:)

[ssnaik84]

 SELECT Top 1 Date,Other,Fields 
 FROM
 (    SELECT Top 10 Date,Other,Fields 
     FROM TheTable
)a
 ORDER BY Date DESC

 SELECT Top 1 Date,Other,Fields 
 FROM
 (    SELECT Top 10 Date,Other,Fields 
     FROM TheTable
     ORDER BY Date DESC
)a
 

this is probably the best solution for such a most commonly asked interview question ;)

[Delerna]

oops
good spot ssnaik84