chap11_8.html

Oracle资料大集合

  <a href="http://www.javascripts.com"><font face="verdana,helvetica" size="1" color="000000">JavaScripts.com</font></a><br>
  <a href="http://www.opensourceit.com"><font face="verdana,helvetica" size="1" color="000000">open source IT</font></a><br>
  <a href="http://www.roadcoders.com"><font face="verdana,helvetica" size="1" color="000000">RoadCoders</font></a><br>
  <a href="http://www.Y2Kinfo.com"><font face="verdana,helvetica" size="1" color="000000">Y2K Info</font></a>
</FONT>

</td>



<!--End Left Navigation column -->
<!-- spacer column  -->

<TD width="40">
<img src="../../../images/dotclear.gif" WIDTH="40" HEIGHT="1" border=0>
</TD>

<!--Begin Content Column -->

<TD VALIGN="TOP" width="500">

<P>




<blockquote>

<b><font size="+1">11.8	How do I匬revent the selection of duplicate rows?</font></b>

<p>

<B>Problem</B>

<p>
In some of my queries, duplicate rows are returned. Even though the records are valid for the query, I don抰 want to return duplicate rows from the query. In many cases, I want to view the unique values within a column. How do I prevent the selection of duplicate rows and present unique results?
<p>
<B>Technique</B>
<p>
The DISTINCT operator causes only unique rows to be returned by a query. If any column in the query makes the row unique, the row is returned. The keyword is placed at the beginning of the select list and needs to be specified only once in the query.
<p>
<B>Steps</B>
<p>
<B>1.</B>	Run SQL*Plus and connect as the WAITE user account. CHP11_20.SQL, shown in <A HREF="image/11oht16.gif" TARGET="_blank">Figure 11.16</A>, creates the sample tables and records used in this How-To.

<p>

The data created in the sample table contains duplicate values for some of the columns. When the data is queried using the DISTINCT operator, the duplicate column values are eliminated. Run the statement to create the table and data.
<p>
<code><b>SQL> START CHP11_20.sql
<p>
Table dropped.
<p>
Table created.
<p>
1 row created.
<p>
1 row created.
<p>
1 row created.
<p>
1 row created.
<p>
Commit complete.</b></code>

<p>

<B>2.</B> Load CHP11_21.SQL into the SQL buffer. The file contains a query of the sample table that does not use the DISTINCT keyword to eliminate duplicate rows.
<p>
<code><b>SQL> GET CHP11_21.sql
<p>
1 SELECT
<p>
2 DEPT_NO
<p>
3 FROM
<p>
4 EMP11
<p>
5*  ORDER BY DEPT_NO</b></code>

<p>

Line 2 specifies the column returned by the query. Line 4 presents the sample table as the source of the data, and line 5 specifies the order of the resulting data.

<p>

<B>3.</B>	Run the statement to show the results.

<p>
<code><b>SQL>  /
<p>
DEPT_NO
<p>
----------
<p>
1
<p>
1
<p>
2
<p>
2
</b></code>
<p>
If the purpose of the query is to display the unique department numbers, the duplicate data makes the query results hard to read. Even though each record contains a unique employee, the DEPT_NO column is duplicated among records. If the source table contains hundreds of records, the results of this query
are unreadable.

<p>

<B>4.</B>	Load CHP11_22.SQL into the SQL buffer. The file contains a query that returns unique department numbers by specifying the DISTINCT keyword before the select list.

<p>

<code><b>SQL>  GET CHP11_22.sql

<p>
1   SELECT
<p>
2      DISTINCT DEPT_NO
<p>
3   FROM
<p>
4      EMP11
<p>
5*  ORDER BY DEPT_NO</b></code>

<p>

Line 2 uses the DISTINCT keyword to prevent the query from returning duplicate department numbers. The remaining lines of the query are identical to the query presented in Step 2.
<p>
<B>5.</B>	Run the statement to display the results.

<code><b>
<p>
SQL> /
<p>
DEPT_NO
<p>
----------
<p>
1
<p>
2
<p></b></code>

Regardless of the number of rows in the table, only distinct departments are returned by the query. The DISTINCT keyword applies to all columns following it in the select list.
<p>
<B>6.</B>	Load CHP11_23.SQL into the SQL buffer. The query returns two columns after the DISTINCT operator.
<p>
<code><b>SQL> GET CHP11_23.sql
<p>
1  SELECT
<p>
2     DISTINCT DEPT_NO, EMP_NO
<p>
3  FROM
<p>
4     EMP11
<p>
5* ORDER BY DEPT_NO
<p></b></code>

Line 2 specifies two columns to be returned by the query. The DISTINCT keyword will cause distinct DEPT_NO, EMP_NO combinations to be returned by the query.

<p>

<B>7.</B> Run the query to display the results.

<p>

<TR><td><code><b>DEPT_NO</b></code></td>
<td><code><b>EMP_NO</b></code></td></TR>

<TR><td><code><b>----------</b></code></td>
<td><code><b>---------</b></code></td></TR>

<TR><td><code><b>1</b></code></td>
<td><code><b>1</b></code></td></TR>

<TR><td><code><b>1</b></code></td>
<td><code><b>2</b></code></td></TR>

<TR><td><code><b>2</b></code></td>
<td><code><b>3</b></code></td></TR>

<TR><td><code><b>2</b></code></td>
<td><code><b>4</b></code></td></TR>

<p>

Even though duplicate DEPT_NO columns are returned by the query, all the rows returned are distinct.

<p>

<B>How It Works</B>

<p>

The DISTINCT keyword is used to prevent queries from returning duplicate rows. Step 1 creates the sample tables and records used throughout this How-To. Steps 2 and 3 query the records in the table without using the DISTINCT keyword. Steps 4 and 5 use the DISTINCT keyword to return distinct DEPT_NO columns from the sample table. Steps 6 and 7 show the results of a query with two columns specified in a select list using the DISTINCT keyword.

<p>

<B>Comments</B>

<p>

The DISTINCT operator is useful in analyzing data in a table. When you use the DISTINCT keyword, keep in mind that it affects the entire select list. You don抰 need to specify the keyword for each column in the list. The keyword is useful within subqueries to limit the number of rows to be processed by the query.


<!-- </td>

</td>
</tr>
</table> -->
</td>

</td>
</tr>
</table>

<!-- begin footer information -->

<MAP NAME="footer">
<AREA SHAPE=RECT COORDS="0,0,62,26" HREF="http://www.itlibrary.com/">
<AREA SHAPE=RECT COORDS="62,0,135,26" HREF="http://www.developer.com/about/">
<AREA SHAPE=RECT COORDS="135,0,199,26" HREF="http://www.itlibrary.com/search/">
<AREA SHAPE=RECT COORDS="200,0,274,26" HREF="http://www.itlibrary.com/subscribe/">
<AREA SHAPE=RECT COORDS="275,0,335,25" HREF="http://www.developer.com/contact/adinfo.html">
<AREA SHAPE=RECT COORDS="335,0,417,25" HREF="http://www.developer.com/contact/">
<AREA SHAPE=RECT COORDS="418,0,467,26" HREF="http://www.developer.com/about/faq.html">
</MAP>

<P>
<table width="640" cellpadding="0" cellspacing="0" border="0">
<tr>
<TD WIDTH="130"></TD>
<td width="468">
<IMG SRC="../../../images/footer/footerfile.gif" ALT="footer nav" width="467" height="26" BORDER="0" usemap="#footer" ismap>
</td>
</tr>
<tr>
<TD WIDTH="130"></TD>
<td width="468"> 
<font face=arial,helvetica size="1"> Use of this site is subject certain <a href="http://www.developer.com/legal/">Terms &amp; Conditions.</a><br>
Copyright (c) 1996-1999 <A HREF="http://www.earthweb.com/">EarthWeb, Inc.</A>.  All rights reserved.  Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited.
<a href="http://www.earthweb.com/corporate/privacy.html">Please read our privacy policy for details.</a>
</td>
</tr>
</table>

</BODY>
</HTML>