473,837 Members | 1,722 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to reterive the last 5 records in a table?

Hi

I have a table which contains number of rows. I want to fetch the
last 5 records from the table. I know for the first 'n' records we can
use FETCH FIRST n ROWS ONLY command. But i want to fetch last 5
records. Can any one help in this...

Advance Thanks for ur replies
Warm Regards
Guru

Nov 12 '05
20 40118
Guru wrote:
Hi

In MYSQL we can use the select * from table limit -5 to
reterive the last five records from the table. Like wise i want to know
the equivalent in DB2.
Warm Regards
Guru

How do you know MySQL didsn't give you the first 5 rows? ;-)
FETCH FIRST, LIMIT, TOP mean exactly one thing without an ORDER BY:
Give me ANY 5 rows.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #11
Knut Stolze wrote:
Nunya Bizness wrote:

FETCH FIRST 5 ROWS does NOT fetch the first five records from the table
UNLESS you have added a clustered index to the table and you have
included an order by clause to your select statement that causes the
optimizer to select the clustered index in its plan. (Technically, this
is only guaranteed to be true if the select statement is run immediately
after the clustered index is created (or a table reorg is performed) and
before the next table insert is performed.)

You don't need a clustered index for that, though it might help
performance-wise. Only the ORDER BY determines the order in which the rows
are returned. In all other cases, you can't rely on any specific order of
the rows. After all, SQL is a set-oriented query language, and the
elements in a set are - per definition - not sorted.

Mayhaps I read the question a mite too literally. When someone says they
want the first five records in a table, or the last five in this case,
being anal, I assume they mean exactly what they said - the first five
(physical) records in the table. The only way to do that is as I stated.

A simple order by clause, as explained in my original response, will
return the same key values in the first five records but they are not
necessarily the first five physical records in the table and, for that
matter, they are not necessarily the same five records every time.

In actual truth, assuming that the table consists of nothing but exact
duplicates in the key values and there are at least 6 records in the
table, even the method that I outlined is insufficient to guarantee that
the database will return the first five physical records in the table
every time the query is run.

If you doubt the truth of what I say, a simple test will prove me right
or wrong. Simply create a table with two columns - the key column and a
second column. Populate the table with enough records to force db2 to
use the clustered index once you create it. (For such a skinny table,
this may require a substantial number of rows.) Ensure that you insert
duplicate values into the key column and place unique values into the
second column so you can tell the difference between the returned rows.
(Think of the second column as the table row number.) After you create
the table, create a clustered index on the key column and do a runstats
on the table to ensure the system catalogs are updated with the most
recent information. Create a 'control' by running your select ... order
by key column statement against this table and write the result set to a
file on disk. (Make sure you select BOTH columns from the table in your
select statement.) Then, run the exact same query and write the result
set to a different file. Diff the two files and observe the result.
(Note that it may be necessary to perform the second select and
subsequent diff several times, possibly after several other queries are
run to force db2 to clear the buffers and re-read the table, before the
behavior I describe manifests itself.)
Nov 12 '05 #12
Nunya Bizness wrote:
Mayhaps I read the question a mite too literally. When someone says they
want the first five records in a table, or the last five in this case,
being anal, I assume they mean exactly what they said - the first five
(physical) records in the table. The only way to do that is as I stated.


From that perspective your answer is understood, but nevertheless doesn't
hold true. DB2 can return the rows in any order, regardless of the
physical distribution, unless an ORDER BY was given. (As someone else
mentioned in this thread, DPF is a good way to demonstrate that.)

The whole point of relational database systems is that the user should not
worry how data is physically stored. (You have to keep in mind that the
physical storage doesn't have to be aligned with the order of the insert of
the rows.) In short, the whole concept of "first row" is pretty much
meaningless in SQL without any order being specified, and the only way for
that is to specify an ORDER BY - end of story.

(If someone really wants to get the physically first n rows, I would start
questioning the reasons for the requirement - as was done here.)

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #13
"Knut Stolze" <st****@de.ibm. com> wrote in message
news:cv******** **@fsuj29.rz.un i-jena.de...
snip
In short, the whole concept of "first row" is pretty much
meaningless in SQL without any order being specified, and the only way for
that is to specify an ORDER BY - end of story.
snip
Knut Stolze


One exception is the GROUP BY. DB2 will order the rows by the columns in a
GROUP BY because it needs to sort the rows to group them. This is not
guaranteed by the specs, but it is often worth omitting an ORDER BY if it is
redundant with the GROUP BY to improve performance.
Nov 12 '05 #14
Mark A wrote:
"Knut Stolze" <st****@de.ibm. com> wrote in message
news:cv******** **@fsuj29.rz.un i-jena.de...
snip
In short, the whole concept of "first row" is pretty much
meaningless in SQL without any order being specified, and the only way for
that is to specify an ORDER BY - end of story.
snip
Knut Stolze

One exception is the GROUP BY. DB2 will order the rows by the columns in a
GROUP BY because it needs to sort the rows to group them. This is not
guaranteed by the specs, but it is often worth omitting an ORDER BY if it is
redundant with the GROUP BY to improve performance.

That is not correct. The optimizer will decide whether it has to do a
SORT explicitly or can rely on an some other technique.
Counter examples for GROUP BY not implying ORDER are hashjoin, MQT,
GROUP BY/DISTINCT in DPF where the partitioning key part of the group
by. The later can be extended to all sorts of partitioning (UNION ALL
views, range partitioning, ...).
The golden rule of SQL: Tell what you want. Make no assumption on how
the RDBMS will get it.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #15
Knut Stolze <st****@de.ibm. com> writes:
Nunya Bizness wrote:
Mayhaps I read the question a mite too literally. When someone says they
want the first five records in a table, or the last five in this case,
being anal, I assume they mean exactly what they said - the first five
(physical) records in the table. The only way to do that is as I stated.

From that perspective your answer is understood, but nevertheless doesn't
hold true. DB2 can return the rows in any order, regardless of the
physical distribution, unless an ORDER BY was given. (As someone else
mentioned in this thread, DPF is a good way to demonstrate that.) The whole point of relational database systems is that the user should not
worry how data is physically stored. (You have to keep in mind that the
physical storage doesn't have to be aligned with the order of the insert of
the rows.) In short, the whole concept of "first row" is pretty much
meaningless in SQL without any order being specified, and the only way for
that is to specify an ORDER BY - end of story. (If someone really wants to get the physically first n rows, I would start
questioning the reasons for the requirement - as was done here.) --
Knut Stolze
Information Integration
IBM Germany / University of Jena


What if the requirement is to retrieve the last n records from a
particular table by order of which they were inserted.
Many people would simply state: "give me the last 5 records inserted
into a table". As stated, you cannot rely on internal physical order.
You need to have a field keep track of it (e.g. DATE_TIME_INSER TED)
which you can sort on in reverse order and display the first n rows.

Wolfgang
--
Wolfgang Richter, Systems Consultant E-mail: wo******@sfu.ca
Academic Computing Services
Simon Fraser University Telephone: (604) 291-4449
Burnaby, B.C. Canada V5A 1S6 Fax: (604) 291-4242
--
Wolfgang Richter, Systems Consultant E-mail: wo******@sfu.ca
Academic Computing Services
Simon Fraser University Telephone: (604) 291-4449
Burnaby, B.C. Canada V5A 1S6 Fax: (604) 291-4242
Nov 12 '05 #16
Knut Stolze wrote:
Nunya Bizness wrote:

Mayhaps I read the question a mite too literally. When someone says they
want the first five records in a table, or the last five in this case,
being anal, I assume they mean exactly what they said - the first five
(physical) records in the table. The only way to do that is as I stated.

From that perspective your answer is understood, but nevertheless doesn't
hold true. DB2 can return the rows in any order, regardless of the
physical distribution, unless an ORDER BY was given. (As someone else
mentioned in this thread, DPF is a good way to demonstrate that.)

I believe that I specified the use of an order by clause in my original
response so I do not quite understand how you can state that my response
does not hold true in the first sentence and then proceed to qualify
that with 'unless an ORDER BY was given' in the very next sentence.
The whole point of relational database systems is that the user should not
worry how data is physically stored. (You have to keep in mind that the
physical storage doesn't have to be aligned with the order of the insert of
the rows.) In short, the whole concept of "first row" is pretty much
meaningless in SQL without any order being specified, and the only way for
that is to specify an ORDER BY - end of story.' I certainly understand that the origins of relational databases are
firmly rooted in set theory and that, as a result, the concept of
'first' and 'last' rows is just a convenient metaphor, but many people
do not. That does not make the terminology of 'first' and 'last'
meaningless or useless, it simply puts it in perspective. Most people
think of first and last in terms of the actual table, not the result set
- which is merely a subset of the actual table.
(If someone really wants to get the physically first n rows, I would start
questioning the reasons for the requirement - as was done here.)

That was largely my point when I replied the first time but, rather than
simply state that the question was pointless since, without an order by
clause, the order in which the rows in the result set are returned is
non deterministic (i.e. random), I showed how the request COULD be done
and tried to offer up a bit of education/enlightenment.

I had hoped that a deeper explanation of how the records in a table are
organized, accessed and returned would not only answer the original
question but also impart a bit deeper understanding of how databases
work - if not to the original poster, then to any casual reader who was
interested enough to read my entire answer.

To do otherwise would have been a disservice to anyone reading this
thread as it may have perpetuated a fundamental misunderstandin g about
the way relational databases work in their mind.


Nov 12 '05 #17
Wolfgang Richter wrote:
Knut Stolze <st****@de.ibm. com> writes:
Nunya Bizness wrote:

Mayhaps I read the question a mite too literally. When someone says they
want the first five records in a table, or the last five in this case,
being anal, I assume they mean exactly what they said - the first five
(physical) records in the table. The only way to do that is as I stated.

From that perspective your answer is understood, but nevertheless doesn't
hold true. DB2 can return the rows in any order, regardless of the
physical distribution, unless an ORDER BY was given. (As someone else
mentioned in this thread, DPF is a good way to demonstrate that.)

The whole point of relational database systems is that the user should not
worry how data is physically stored. (You have to keep in mind that the
physical storage doesn't have to be aligned with the order of the insert
of
the rows.) In short, the whole concept of "first row" is pretty much
meaningless in SQL without any order being specified, and the only way for
that is to specify an ORDER BY - end of story.

(If someone really wants to get the physically first n rows, I would start
questioning the reasons for the requirement - as was done here.)


What if the requirement is to retrieve the last n records from a
particular table by order of which they were inserted.
Many people would simply state: "give me the last 5 records inserted
into a table". As stated, you cannot rely on internal physical order.
You need to have a field keep track of it (e.g. DATE_TIME_INSER TED)
which you can sort on in reverse order and display the first n rows.


Exactly.

One has to keep in mind that database system usually place the rows on pages
(often 4K in size but the size can vary). Now assume you insert 500 rows.
They go onto the first 10 pages. Now you delete 100 of these rows, which
happen to be on pages 1, 2 and 4. Again you insert 10 rows. Where will
those new rows be placed? They could easily be on pages 1 and 2 and the
free space on those pages is simply reused to not waste any space.
So an identifying attribute like IDENTITY or insert-timestamp that is
every-increasing for each and every row is a must. And with that
attribute, one can do the above: apply an ORDER BY (descending), and fetch
the first 5 rows - you will get the last 5 inserted.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #18
Nunya Bizness wrote:
Knut Stolze wrote:
Nunya Bizness wrote:

Mayhaps I read the question a mite too literally. When someone says they
want the first five records in a table, or the last five in this case,
being anal, I assume they mean exactly what they said - the first five
(physical) records in the table. The only way to do that is as I stated.

From that perspective your answer is understood, but nevertheless doesn't
hold true. DB2 can return the rows in any order, regardless of the
physical distribution, unless an ORDER BY was given. (As someone else
mentioned in this thread, DPF is a good way to demonstrate that.)


I believe that I specified the use of an order by clause in my original
response so I do not quite understand how you can state that my response
does not hold true in the first sentence and then proceed to qualify
that with 'unless an ORDER BY was given' in the very next sentence.


Yes, you did say that. I was only stating the fact that a clustered index
is not needed in the first place.
Most people
think of first and last in terms of the actual table, not the result set
- which is merely a subset of the actual table.


A (result) set is a table and a table is a set.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #19
Knut Stolze wrote:
Nunya Bizness wrote:

Knut Stolze wrote:

Nunya Bizness wrote:

Mayhaps I read the question a mite too literally. When someone says they
want the first five records in a table, or the last five in this case,
being anal, I assume they mean exactly what they said - the first five
(physical ) records in the table. The only way to do that is as I stated.
From that perspective your answer is understood, but nevertheless doesn't
hold true. DB2 can return the rows in any order, regardless of the
physical distribution, unless an ORDER BY was given. (As someone else
mentioned in this thread, DPF is a good way to demonstrate that.)


I believe that I specified the use of an order by clause in my original
response so I do not quite understand how you can state that my response
does not hold true in the first sentence and then proceed to qualify
that with 'unless an ORDER BY was given' in the very next sentence.

Yes, you did say that. I was only stating the fact that a clustered index
is not needed in the first place.


OK, you got me. I concede the point that a clustered index is not an
absolute requirement. In fact, no index at all is required if you want
to get truly technical. The clustered index is merely a means to put the
table into the only state possible where we can 'guarantee' that we are
fetching the first five physical records from a table. (Somebody who
wanted to split hairs would say that the 'guarantee' only applies if we
do not have duplicate key values in the first six key values, but for
the purposes of discussion, I am assuming that to not be the case.)

The state to which I am referring is a freshly re-orged table that has
had no records inserted into it after the reord was performed. A
clustered index is one way of re-orging a table. An export, sort and
import/load is another.

Unfortunately, db2 does not allow us to:
insert into table b select * from table a order by key columns

or we would have a third way that is actually much more efficient than
either of the two available to us currently.

Or did you have a slightly different explanation in mind? (I'd be
interested in hearing it if you did.)
Most people
think of first and last in terms of the actual table, not the result set
- which is merely a subset of the actual table.

A (result) set is a table and a table is a set.


Semantics. I won't quibble over words.
Nov 12 '05 #20

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
3744
by: Lyn | last post by:
I have a form set to Single Form mode with which I can cycle through the records in a table via Next and Previous buttons. To avoid users pressing the Previous button on the first record and the Next button on the last record, I would like to disable one or both buttons when the first and/or last record is displayed. I am not sure how to do this when the RecordSource is simply the table. I know that if the record source were a...
5
1440
by: booksnore | last post by:
I am reading some very large files greater than 10 GB. Some of the files (not all) contain a header and footer record identified by "***" in the first three characters of the record. I need to delete the header or footer record before reading the file into a database. Whats the best way to do this in C#? Any help appreciated. Joe *** Sent via Developersdex http://www.developersdex.com ***
2
15288
by: schapopa | last post by:
Hi, I want to create query where I could group records by quarters, and get the last record in each group. e.g Create Table MyTable ( Value , date )
4
5150
by: Tomas | last post by:
I'm creating MS Access database and I need to have in query an average of 3 last records. How to do it? Maybe here is some function or sql expresion? Thanks
2
1475
by: perryche | last post by:
I don't know where to begin search for this... I want to add a record, but still able to see the page, without bringing it to a new page and have to scroll back to see other records. I hope I explain myself here. Or, is there a way to add record being on the top of other records and not go to the bottom? Thanks. Perry
1
2265
by: jith87 | last post by:
hi, i am importing a text file into oracle database using sql loader.i need to ommit the first and last records of the text file while importing... can anybody help???? this is my text file... A: Removable 0.0% C: Fixed NTFS 39.06 GB 26.10 GB 66.8% D: Fixed NTFS 43.95 GB 30.44 GB 69.3% E:...
10
2312
by: jambonjamasb | last post by:
Hi all, I am new to this and am looking for some direction I have the ideas, but am having trouble putting it into practice. Any help would be greatly appreciated. I have set up three tables. Table one this contains 20 fields. This data is uploaded to access by a macro in access from excel.
4
1488
by: PeteM | last post by:
Using SQL Server 2005 I have on a number of occasions, succesfully used the following code to create tables with the last dated results or observations for each Patient. However I am now being asked to do the same in Access 2003 and all the variations I can think of return the error message “At most one record can be returned by this subquery”. Do I have to give up with Access? SELECT Patient, (SELECT TOP 1 t1.LabResult1 FROM ...
4
4897
by: Greg (codepug | last post by:
I want to display the last 22 records in my continuous form. I have writen the following code and it works, but was wondering if there were any better suggestions for accomplishing this. My code scrolls the screen as it goes to bottom and than scrolls out the 22 recs. My code: Private Sub GoToBottom()
5
1942
by: asdasd10 | last post by:
i tired do it alone but its gives me an eror like that: "r object cant move back" here is my code : <% path = Server.mappath("../db/ServerData.mdb") set con = Server.createobject("ADODB.Connection") con.open "driver={Microsoft Access Driver (*.mdb)};DBQ="&path set r = con.execute("select * from News") Dim i
0
9853
marktang
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...
1
10644
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,...
0
10289
tracyyun
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...
0
9423
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7827
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5681
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...
0
5864
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4482
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
3
3131
bsmnconsultancy
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...

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.