473,289 Members | 1,963 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,289 software developers and data experts.

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 #1
20 39992
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
"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
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
"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
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
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
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
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
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
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.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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
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...
5
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...
2
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
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
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...
1
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... ...
10
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. ...
4
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...
4
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...
5
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 =...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
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: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
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: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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)...

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.