Home arrow Oracle arrow Limiting Rows When Retrieving Table Data

Limiting Rows When Retrieving Table Data

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

How to use the DISTINCT keyword to eliminate duplicate rows

By default, all of the rows in the base table that satisfy the search condition in the WHERE clause are included in the result set. In some cases, though, that means that the result set will contain duplicate rows, or rows whose column values are identical. If thatís not what you want, you can include the DISTINCT keyword in the SELECT clause to eliminate the duplicate rows.

Figure 3-9 illustrates how this works. Here, both SELECT statements retrieve the vendor_city and vendor_state columns from the Vendors table. The first statement, however, doesnít include the DISTINCT keyword. Because of that, the same city and state can appear in the result set multiple times. In the results shown in this figure, for example, you can see that ďAnaheim CAĒ occurs twice. In contrast, the second statement includes the DISTINCT keyword, so each city/state combination is included only once.

A SELECT statement that returns all rows

SELECT vendor_city, vendor_state
FROM vendors
ORDER BY vendor_city


(122 rows selected)

A SELECT statement that eliminates duplicate rows

SELECT DISTINCT vendor_city, vendor_state
FROM vendors
ORDER BY vendor_city


(53 rows selected)

Description

  1. The DISTINCT keyword prevents duplicate (identical) rows from being included in the result set.
  2. The ALL keyword causes all rows matching the search condition to be included in the result set, regardless of whether rows are duplicated. Since this is the default, itís a common practice to omit the ALL keyword.
  3. To use the DISTINCT or ALL keyword, code it immediately after the SELECT keyword.

--------------------------------------------Figure 3-9 How to use the DISTINCT keyword to eliminate duplicate rows



 
 
>>> 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: