Manage Learn to apply best practices and optimize your operations.

How to get the last 50 rows in a table

How do I get the last 50 records in a database table depending on a primary key and without using minus operator?

According to relational database theory, there is no order of rows or columns in a table. A table is just a set of records. In mathematical set theory, there is no concept of the first record in a set. A set has records, and they are in no particular order.

Since you want the last 50 records depending on a primary key, then you are most likely looking for a logical order based on the primary key column(s). Luckily, you can accomplish this quite easily if you are using Oracle 8i! To show you how I can do this, I will show you the contents of one view:

   SQL> select * from all_users;

   USERNAME                          USER_ID CREATED
   ------------------------------ ---------- ---------
   SYS                                     0 14-NOV-00
   SYSTEM                                  5 14-NOV-00
   OUTLN                                  11 14-NOV-00
   DBSNMP                                 16 14-NOV-00
   DISTRIB                                47 25-JAN-01
   AURORA$JIS$UTILITY$                    25 14-NOV-00
   OSE$HTTP$ADMIN                         26 14-NOV-00
   AURORA$ORB$UNAUTHENTICATED             27 14-NOV-00
   ORDSYS                                 28 14-NOV-00
   ORDPLUGINS                             29 14-NOV-00
   MDSYS                                  30 14-NOV-00
   CTXSYS                                 33 14-NOV-00
   SCOTT                                  35 14-NOV-00
   PEASLAND                               41 11-DEC-00
   ADMIN                                  45 23-JAN-01
   HEATUSER                               44 23-JAN-01
   MTSSYS                                 40 14-NOV-00

17 rows selected.

Now let's suppose that I want the last 5 username's from this view (alphabetically). Then I can issue the following query:

   SQL> select username from all_users
     2  where rownum <= 5
     3  order by username desc;


Oracle 8i lets me use the ROWNUM pseudo-column with the ORDER BY clause. To find the last xx rows, I had to use the DESC clause to order the rows in descending order.

For More Information

This was last published in May 2001

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.