a
 
SQLRef.Com 

            SqlRef.Com


SQL BASIC
DATA QUERY LANGUAGE
BASIC FUNCTION
AGGREGATE FUNCTION
DATA MANIPULATION LANGUAGE
DATA DEFINITION LANGUAGE
DATA CONTROL LANGUAGE
OTHERS

How To

How to retrieve only duplicate values from a table ?

Solution

SELECT <column> FROM <table> GROUP BY <column> HAVING COUNT(*) > 1

You can use GROUP BY to retrieve duplicate records in a table as shown in the above SQL. Let's look at the following example.

Table: Customers
CustID Name Address City Country PostCode Phone
CUST001 Maria Anders Obere Str. 57 Berlin Germany 12209 030-0074321
CUST002 Ernst Handel Kirchgasse 6 Graz Austria 8010 7675-3425
CUST003 Helen Bennett Garden House Crowther Way Cowes UK PO31 7PJ (198) 555-8888
CUST004 Paula Wilson 2817 Milton Dr. Albuquerque USA 87110 (505) 555-5939
CUST005 Liu Wong 55 Grizzly Peak Rd. Butte USA 59801 (406) 555-5834

SELECT Country FROM Customers
GROUP BY
Country
HAVING COUNT(*) > 1

The above SQL statement will return the following result.

Country Expression
USA 2



 
| Freebies | Free Trial | Add to Bookmark | Search | Link to Us |
Copyright © 2006 SqlRef.com. All rights reserved.