Tuesday, February 8, 2011

SQL DISTINCT on Multiple Columns

The DISTINCT clause works in combination with SELECT and gives you unique date from a database table or tables. The syntax for DISTINCT is show below

SELECT DISTINCT "column_name"
FROM "table_name"

If you want a DISTINCT combination of more than one column then the syntax is

SELECT DISTINCT column1, column2
FROM "table_name"

Let's look at some examples to understand the usage of the DISTINCT keyword. First, let's create a table for our illustration and insert some data.

CREATE TABLE DuplicateTest(
Firstname
nvarchar (30) NOT NULL,
Lastname
nvarchar(30) NOT NULL,
PostalCode
nvarchar(15) NOT NULL,
City
nvarchar(30) NOT NULL
)
insert into DuplicateTest
(Firstname,Lastname,PostalCode,City)
values
('Param', 'Singh', 'B283SP', 'Birmingham'),
('Steve', 'White', 'EC224HQ', 'London'),
('Mark', 'Smith', 'L324JK', 'Liverpool'),
('Claire', 'whitehood', 'M236DM', 'Manchester'),
('Param', 'Singh', 'B283SP', 'Birmingham')

select * from DistinctTutorial
DuplicateTest
Firstname Lastname PostalCode City
Sarvesh Singh B263SP Birmingham
Steve White EC224HQ London
Mark Smith L324JK Liverpool
Claire whitehood M236DM Manchester
Param Singh B283SP Birmingham

In the result set above there are repetitions in the City Column. Let's get a list of all cities without repeating them using DISTINCT.

select DISTINCT City from DuplicateTest
City
Birmingham
Liverpool
London

Manchester


You can see 'Birmingham' is just returned once in this result, even though it appears more than once in the table. You can get the same result using GROUP BY as shown below.

select city from DuplicateTest
group by city

Let's now use DISTINCT with more than one column. We will add the Lastname column in as well.

select DISTINCT City,Lastname from DuplicateTest
Distinct City and lastname
City Lastname
Birmingham Singh
Liverpool Smith
London White
Manchester whitehood

We get a list of results that have multiple rows, none of which are duplicated.

Again, you can get the same result by using GROUP BY as shown below:

select city,lastname from DuplicateTest
group by city, lastname

If you look at the original data, there are two users with same Lastname (Singh) who live in the same city (Birmingham). With the DISTICT keyword you get one unique row. Let's now add another column to our SELECT query.

select DISTINCT City,Lastname,Postalcode from DuplicateTest

This returns:

City Lastname PostalCode
Birmingham Singh B263SP
Birmingham Singh B283SP
Liverpool Smith L324JK
London White EC224HQ
Manchester whitehood M236DM

You will notice now that you are seeing two rows with the same lastname of Singh. This is because their 'Postalcode' is different, and the addition of that column makes the rows unique.

Again you will get the same result using GROUP BY as shown below:


select city, lastname, postalcode
from DuplicateTest
group by city, lastname, postalcode

Let's look at another example where you can use DISTINCT on multiple columns to find duplicate addreses. I've taken this example from the post. Please refer to this post for more detail.

SELECT PostCode, COUNT(Postcode)
FROM
(
SELECT DISTINCT Address1, Address2, City, Postcode
FROM AddressTable
) AS Sub
GROUP BY Postcode
HAVING COUNT(Postcode) > 1

Or you can use GROUP BY as follows:

SELECT Address1,Address2,City,PostCode,Count(PostCode)
FROM AddressTable
GROUP BY Address1,Address2,City,PostCode
HAVING Count(PostCode) > 1

In both of these cases, we are using DISTINCT to find those rows that are duplicates, based on the columns we include in the queries.

DISTINCT can also be used to get unique column values with an aggregate function. In the example below, the query gets the unique values of reorderpoint and then does a SUM.

USE AdventureWorks
GO
SELECT SUM(DISTINCT ReorderPoint) as DistinctSum
FROM Production.Product
GO

Result: 1848 rows

In the example below query is doing a SUM of ReorderPoint including the duplicates.

SELECT SUM(ReorderPoint) as WithoutDistinct
FROM Production.Product
GO

Result: 202287 rows

As you can see from the above two examples the importance of DISTINCT with an aggregate function. The user could end up un-knowingly using completely incorrect SUM had he used the result from the second query if the requirement was to get the SUM of unique values of ReorderPoint.

The purpose of the article was to show a user simple usage of DISTINCT. I hope you would find this article useful.

No comments: