473,324 Members | 2,581 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

how to display nth record from table

how to display nth record from table
Dec 20 '09 #1
5 4314
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

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

Similar topics

0
by: dotyet | last post by:
Hey All, I have a small problem. consider the following >db2 "create table ttt (mynum float, yournum double )" DB20000I The SQL command completed successfully. >db2 "describe table ttt" ...
0
by: Carl | last post by:
I have a main form with navigation buttons on it and a label showing for example Record 1 of 15 using recordsetclone on it and eveything works fine. When I move through the records the record...
3
by: SJM | last post by:
I have a problem that occurs occasionally with a db for a undetermined reason which I would love to solve. I construct and append a series of 7 records to a table using ADO recordset. Each record...
11
by: bala | last post by:
hi!!! i need to display a disclaimer which is two page in length in a word document. i also need to format the text. the idea is something as follows on opening the application, a form which...
12
by: Wadim Grasza | last post by:
I want to store and display (on a form or a report) multiple pictures per record in an access database. The pictures are not stored within the database. They are stored as files and the database...
0
by: M. David Johnson | last post by:
I cannot get my OleDbDataAdapter to update my database table from my local dataset table. The Knowledge Base doesn't seem to help - see item 10 below. I have a Microsoft Access 2000 database...
9
by: Greg | last post by:
Hi, I have a table with "dates", i'd like to display those dates on a calendar. I've put a calendar in a form, linked to my "date" field, and it works, but only showing one "date" per calendar....
1
by: Dan Somdahl | last post by:
Hi, I am new to ASP but have what should be a fairly simple task that I can't figure out. I need to parse a string from a single, semi-colon delimited, 60 character field (el_text) in a recordset...
4
by: seth_hickel | last post by:
With other solutions I would get a recordset, read each record and display data by formating my html as I wanted to display values of each record. I am trying to display data in a three column...
3
by: phil67b | last post by:
Hello everybody, I have a page rech.php where I'm doing a multi-criteria research Ex. choose your car model, choose your country. After validation of my form, on the same page, the lines will be...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.