Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Aug 18, 2000

Simple SQL: Pt. 1 - Page 3

By Ted Brockwood

Getting Acquainted with DISTINCT

An excellent keyword to get acquainted with is "DISTINCT". If you have data that is duplicated, but you only want to see one instance of it, DISTINCT is just the ticket.

For example, you have a table named CUSTOMERS. It is loaded with the names, addresses, phone numbers, and company names of all your best customers. Your boss needs to know what companies have been buying from you. Frequently you have multiple buyers from the same company, perhaps different divisions, so running a standard SELECT statement like this:

SELECT FIRST_NAME, LAST_NAME, COMPANY
       FROM CUSTOMERS;

will return something like this:

FIRST_NAMELAST_NAMECOMPANY
JoeJonesIsoLunacy, Inc
DonnaSprintMegaData Systems Corp
WillBrownIsoLunacy, Inc

As is obvious, you have two customers from IsoLunacy, Inc in your query results. If this were thousands of duplicates, it would be a terrible headache to create a list manually of companies you deal with. So, you fire off a DISTINCT query such as this:

SELECT DISTINCT COMPANY FROM CUSTOMERS;

Which returns:

COMPANY
IsoLunacy, Inc.
MegaData Systems Corp

SELECT statements are also adept at handling comparisons such as greater than and less than. Imagine the table "EMPLOYEES" with Names, Addresses, Start dates, vacation days accrued, and vacation days taken contained within it. The boss wants to know (for the Human Resources Intranet) whether anyone has taken more vacation days than they have accrued. Your SELECT might look like this:

SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEES
       WHERE VACATION_TAKEN > VACATION_ACCRUED;

The results might look like:

FIRST_NAMELAST_NAME
JoeSmith

Good information for HR, and bad information for the employee names resulting from this query.



SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM