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

How to reterive the last 5 records in a table?

P: n/a
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 #1
Share this Question
Share on Google+
20 Replies


P: n/a
Guru wrote:
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

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.) FETCH FIRST 5 ROWS selects
the first 5 rows from the returned RESULT set and, further, the same 5
rows are not necessarily returned if the select statement is run
repeatedly. (This last statement is especially evident when run against
a parallel database.) This is because the order in which the database
returns qualifying rows selected by a select statement is
non-deterministic (i.e. random).

Given the above, it is therefore understandable that there is not a
FETCH LAST n ROWS command available. If you still desire to examine only
the last five rows returned by your select, the easiest way to do this
(in a shell script or from the command line) would be as follows:

db2 -x "select blah-blah-blah" | tail -5

As explained above, this can yield different results every time it is
run, even if the table you are selecting from does not change.
Nov 12 '05 #2

P: n/a
"Guru" <gu*********@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
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...
Warm Regards
Guru


How about sorting the result set in descending order and then use FETCH
FIRST n ROWS ONLY.
Nov 12 '05 #3

P: n/a
Hi,
But the actually question what guru mean is whatever be the order of
rows retried, he wanted to get last 5 rows..

Thiru.
WantedToBeDBA.

Nov 12 '05 #4

P: n/a
"Thiru" <Wa***********@gmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...
Hi,
But the actually question what guru mean is whatever be the order of
rows retried, he wanted to get last 5 rows..

Thiru.
WantedToBeDBA.

That makes no sense to me. If the rows are not ordered (by index access or
ORDER BY), then why would someone care if they got the last 5 or the first 5
rows. I assume we are trying to address a specific application problem that
is manifested in an SQL statement to retrieve some specific data. I assume
the question is how to retrieve the correct rows, regardless of whether they
are the first 5 or last 5.
Nov 12 '05 #5

P: n/a
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.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #6

P: n/a
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

Nov 12 '05 #7

P: n/a
You don't get the point.
Unless you specify an ORDER BY clause there is no *guaranteed* order in a
relational database. So what is exactly your definition of "the last 5
rows" ?

--
Message posted via http://www.dbmonster.com
Nov 12 '05 #8

P: n/a
Anton Versteeg via DBMonster.com wrote:
You don't get the point.
Unless you specify an ORDER BY clause there is no *guaranteed* order in a
relational database. So what is exactly your definition of "the last 5
rows" ?


Exactly. And if you did specify an ORDER BY then you can simply reverse the
order and fetch the first 5 rows - this will be the same result.

p.s: There was a discussion on MySQL vs. relational database systems here in
this group, and you might want to have a look at that.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #9

P: n/a
Thiru wrote:
Hi,
But the actually question what guru mean is whatever be the order of
rows retried, he wanted to get last 5 rows..

Thiru.
WantedToBeDBA.

SELECT * FROM (SELECT * FROM T ORDER BY pk DESC FETCH FIRST 5 ROWS) AS X
ORDER BY PK;

One could also conceive an OLAP function using windowing, but it's too
early in the morning for me to mess with windowing....

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #10

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
"Knut Stolze" <st****@de.ibm.com> wrote in message
news:cv**********@fsuj29.rz.uni-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

P: n/a
Mark A wrote:
"Knut Stolze" <st****@de.ibm.com> wrote in message
news:cv**********@fsuj29.rz.uni-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

P: n/a
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_INSERTED)
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

P: n/a
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 misunderstanding about
the way relational databases work in their mind.


Nov 12 '05 #17

P: n/a
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_INSERTED)
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

P: n/a
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

P: n/a
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

P: n/a
Serge Rielau wrote:
SELECT * FROM (SELECT * FROM T ORDER BY pk DESC FETCH FIRST 5 ROWS) AS X ORDER BY PK;


This won't work in any versions of DB2 Z/OS. "fullselect" in DB2 Z/OS
doesn't include ORDERBY and FETCH FIRST clauses. Although I haven't had
a need to use it so far, but this clearly shows how useful they can be.
Also missing from fullselect is VALUES clause. I sure wish, IBM irons
out these "small" discrepancies between the two platforms.

P. Adhia

Nov 12 '05 #21

This discussion thread is closed

Replies have been disabled for this discussion.