To visit my site

HOME] Consulting Design Maintenance Project Testing Training Turnkey Java C++ SQL HTML JavaScript C#


To contact us

© 2002 - 2015 All Rights Reserved Total Application Works



  • Tell a friend about this site (copy and paste the following HTML page into a file (e.g., using Notepad).)
  • <html>
    <head>
    <title>Example of a link </title>
    </head>
    <body>
    <a href="http://sumtotalz.com/TotalAppsWorks/SQL/MY_SQL_DISTINCT.html">
    Lists and their Tags </a>
    </table>
    </body>
    </html>




SQL - Distinct Keyword

Objectives

The objective of this lesson is to describe the SQL SELECT DISTINCT command and present an example on how to use this command.

Introduction

The DISTINCT keyword is intended to include only unique rows in the result set. When SQL encounters a Null value, this field is considered empty with no data. Empty fields are considered equal for the purposes of the DISTINCT keyword; only one NULL is selected no matter how many empty fields are encountered. So why is this keyword of interest? To understand the answer, you must remember why SQL is used. SQL is used to access relational databases, and relational databases are used primarily by businesses to run their business. We will describe the Distinct keyword in more detail in the next section.

Distinct Keyword

Let's assume that we own a chain of hardware stores. What does our database look like?
 
+------------+    +-----------------+ 
|  Employee  |    |Employee_Pay     |
+------------+    +-----------------+
| emp_ID     |--->| emp_ID          |
| lastName   |    | position        |
| firstName  |    | dateOfHire      |
| middleName |    | payRate         |
| address    |    | dateOfLastRaise |
| city       |    | bonus           |
| state      |    +-----------------+
| zip        |
| phone      |
| cellPhone  |
| emailAddr  |
+------------+


+------------+    +-------------------+    +--------------+ 
|  Customers |    |    Orders         |    |  Inventory   |
+------------+    +-------------------+    +--------------+
| cust_ID    |-+  | orderNum          | +->| prod_ID      |
| lastName   | +->| cust_ID           | |  | partNum      |
| firstName  |    | prod_ID           |-+  | typeOfTool   |  
| middleName |    | quantity          |    | Brand        | 
| address    |    | orderDate         |    | prodDesc     | 
| city       |    | emp_ID            |    | inStock      |
| zip        |    +-------------------+    | price        |
| phone      |                             +--------------+ 
| cellPhone  |
| emailAddr  |
| typeCust   |
+------------+

Before we use our database, let's interrogate a telephone directory, using the DISTINCT keyword. If we use the following command:
 
SELECT DISTINCT LastName FROM TelephoneDirectory

There might be hundreds of Smith's in the telephone directory, but the DISTINCT keyword will list just the first occurrence. This is probably not what we wanted. Referring back to our hardware store, if past history indicates that people who buy a circular saw are likely to buy a
  • Drill
  • Hammer
  • Nails
  • Screw Driver set
  • Pliers set
  • Wrench set
then I would like to interrogate my database to show who has recently bought a circular saw. If I use the following SQL command:
 
SELECT DISTINCT cust_ID, lastName, firstName
FROM Customer, Order, Inventory
WHERE Customer.cust_ID = Order.cust_ID AND Inventory.prodDesc = 'Circular Saw'
ORDER BY lastName, firstName

The result of the above command is:

 CustomerID  
 LastName  
 FirstName  
3 Howard Stuart
2 Hilloway Joel


The DISTINCT keyword includes only unique rows in the result set. In this example, I am using a small table, but in the case where we could have a table consisting of a million names, using the DISTINCT keyword will allow me to target market my customers.

The format for this command using MS Access is
 
SELECT DISTINCTROW cust_ID, lastName, firstName
FROM Customer, Order, Inventory
WHERE Customer.cust_ID = Order.cust_ID AND Inventory.prodDesc = 'Circular Saw'
ORDER BY lastName, firstName











[
HOME] Consulting Design Maintenance Project Testing Training Turnkey Java C++ SQL HTML JavaScript C#

© 2002 - 2015 All Rights Reserved Total Application Works