Displaying Distinct Rows


Suppose you were interested in learning which customers actually made purchases of products. You can get that information using the following query, which retrieves the purchased_b y column from the purchases table:

  SELECT purchased_by   FROM purchases;  PURCHASED_BY ------------  1  1  4  2  3  2  3  4  3 

You can then scan the purchased_by column from the rows visually to identify each customer who made a purchase. As you can see, it s not easy, because some customers made more than one purchase. If the list were a lot longer and featured more customers, the task would become even more tedious . Wouldn t it be great if you could throw out the duplicate rows that contain the same customer ID? Well, you ll be happy to know that Oracle does indeed have the ability to do that: you can suppress the display of duplicate rows using the DISTINCT keyword. In the following example, the DISTINCT keyword is used to suppress the duplicate rows from the previous query:

  SELECT DISTINCT purchased_by   FROM purchases;  PURCHASED_BY ------------  1  2  3  4 

From this list, it s easy to see that customers #1, #2, #3, and #4 made purchases; the duplicate rows are suppressed. Next, you ll learn an additional way of limiting the rows retrieved in a query.




Oracle Database 10g SQL
Oracle Database 10g SQL (Osborne ORACLE Press Series)
ISBN: 0072229810
EAN: 2147483647
Year: 2004
Pages: 217

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net