Q
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;

   USERNAME
   ------------------------------
   SYSTEM
   SYS
   SCOTT
   PEASLAND
   OUTLN

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


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.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close