Home arrow Oracle arrow Page 2 - Limiting Rows When Retrieving Table Data

How to use the ROWNUM pseudo column to limit the number of rows - Oracle

In this fifth part of a nine-part article series on retrieving data from Oracle databases with the SELECt statement, you'll learn how to use the DISTINCT keyword and ROWNUM pseudo column to limit the number of rows returned. This article is excerpted from chapter three of the book Murach's Oracle SQL and PL/SQL, written by Joel Murach (Murach Publishing; ISBN: 9781890774509).

TABLE OF CONTENTS:
  1. Limiting Rows When Retrieving Table Data
  2. How to use the ROWNUM pseudo column to limit the number of rows
By: Murach Publishing
Rating: starstarstarstarstar / 0
August 09, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

In addition to eliminating duplicate rows, you can limit the number of rows that are retrieved by a SELECT statement. To do that, you can use the ROWNUM pseudo column as shown in figure 3-10. A pseudo column works similarly to a column in a table. However, you can only use a pseudo column to select data. In other words, you canít insert, update, or delete the values stored in a pseudo column.

If you want to learn more about how pseudo columns work, you can search the Oracle Database SQL Reference manual for pseudocolumn. Note that the Oracle documentation doesnít include a space between the words pseudo and column.

The first example shows how to limit the number of rows in the result set to the first five rows. Here, the ROWNUM pseudo column is used in the WHERE clause to return the first five rows in the Invoices table.

The second example shows how to add an ORDER BY clause to sort the first five rows of the table so the largest invoice total is displayed first. Since the sort operation is applied after the first five rows are retrieved, this doesnít retrieve the five largest invoice totals in the Invoices table. Instead, it returns the first five rows of the table and then sorts them.

If you want to return the five largest invoice totals for the entire Invoices table, you need to sort the result set before you use the ROWNUM pseudo column to limit the number of rows included in the result set. To do that, you can use a subquery as shown in the third example. In the FROM clause, this example supplies a SELECT statement that sorts the Invoices table instead of supplying the name of the Invoices table. As a result, the table is sorted before the WHERE clause is applied.

For more information about working with subqueries, see chapter 6. In addition, if the ROWNUM pseudo column isnít adequate for your needs, you might want to use the ROW_NUMBER function described in chapter 8.

A SELECT statement that uses the ROWNUM pseudo column to limit the number of rows in the result set

SELECT vendor_id, invoice_total
FROM invoices
WHERE ROWNUM <= 5

A SELECT statement that sorts the result set after the WHERE clause

SELECT vendor_id, invoice_total
FROM invoices
WHERE ROWNUM <= 5
ORDER BY invoice_total DESC

A SELECT statement that sorts the result set before the WHERE clause

SELECT vendor_id, invoice_total
FROM (SELECT * FROM invoices
ORDER BY invoice_total DESC)
WHERE ROWNUM <= 5

Description

  1. You can use the ROWNUM pseudo column to limit the number of rows included in the result set.
  2. If you want to sort the result set before you use the ROWNUM pseudo column to limit the number of rows included in the result set, you can use a subquery as shown in the third example. For more information about working with subqueries, see chapter 6.

--------------------------------------------Figure 3-10 How to use the ROWNUM pseudo column

Please check back regularly for the continuation of this series.



 
 
>>> More Oracle Articles          >>> More By Murach Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: