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
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our Oracle gurus are waiting to answer your toughest questions.
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.