how to display nth record from table
5 4339
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: 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 1,134
Recognized Expert Top Contributor
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 1,134
Recognized Expert Top Contributor
nbiswas posted while I was writing mine
Oh well you now have plenty of options to choose from
:)
- 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 1,134
Recognized Expert Top Contributor Sign in to post your reply or Sign up for a free account.
Similar topics |
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"
Column Type Type
|
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 number changes
fine. Within this main form I have a subform detailing distribution
records for the contact (main form is based on this) that also has
navigation buttons and a label showing Distribution 1 of ##. This is
where the problem lies. The...
|
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 represents a day of the week, and the code appends a
record starting with Monday and finishing with Sunday. After appending the 7
records to a table I then refresh the screen and display the records in a
subform for the user to edit. What happens...
|
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 serves as splash screen with
the disclaimer of the application is shown. now the problem i am
running into is in the display of the disclaimer which is really two
page long and it should be formatted too. using a textbox seems not
|
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 contains the paths
to the
pictures.
The database consists of two tables:
TABLE DATA
ID Name LastName
| |
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 which indexes
computer magazine articles for personal reference.
I am developing a Visual Basic.NET program whose sole
purpose is to enter new records into the database. No
updates to existing entries, no deletions, and no display
|
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.
I can see all "dates" moving to next records, but one per calendar. :-(
Ex: 17/06 on one claendar, then if i click on next record, I can see my
calendar with 24/06 ...etc ...
|
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 and display the results in a table on a
webpage (ASP)
I can retrieve the recordset from the database and display the field data
results in rows of a table but have the entire 60 character string in one
cell. I need to break that string apart...
|
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 three row
table with paging. How does this translate to ASP.NET - or - what
ASP.NET tools do I use to accomplish this and how.
Thank you.
|
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
displayed (I put a max limitation of 500 lines). Ex. list of cars Fiat
to buy in UK.
A clic on a line will bring me to the display page disp.php Ex. I will
clic on the car n° 5 =<a href="disp.php?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |