SQL Server – SELECT DISTINCT on multiple Columns

In SQL Server Select Distinct clause can be used to retrieve distinct records on a particular column or can be used on multiple columns. This clause is helpful in identifying unique and distinct database records in a table containing large amount of data. Example includes fetching distinct CustomerIDs from Order table, fetching distinct EmployeeIDs from a EmployeeEntry table.

SQL Distinct clause will eliminate rows having same values for a particular column or multiple columns and will return only one record for those entries.

SQL Server – Using Select distinct on multiple columns:

To retrieve distinct records on two or more columns in SQL server simply use distinct clause as below:

Select distinct columnName1, columnName2, columnName3, columnName4 from tableName

Example:

Let’s say we have a Order table and we want to identify distinct postalCode with their CustomerIDs for delivery purpose, we can use below query:

select distinct CustomerId,ShipPostalCode from orders order by ShipPostalCode asc

Result:

When using DISTINCT clause for multiple columns at once, SQL will consider all the supplied columns as one and return a row if it is distinct. A combination of all columns will be considered for distinct records and not individual column.

This query returns all the distinct CustomerId and ShipPostalCode distinct entries. Here the Distinct query will consider both the columns and return all the distinct columns against the other column entry. In the above result image you can see row no: 11,12 & 19,20,21. The ShipPostalCode is same (i.e. 05033 for 11,12 & 1010 for 19,20,21) but the CustomerId is different for those PostalCodes.

Example – Using Select distinct on more than two columns:

Let’s consider we want to identity the distinct records for CustomerId, PostalCode, ShipCity and ShipVia from the order table, for this we can use below query:

select distinct CustomerId,ShipPostalCode,ShipCity,ShipVia
from orders order by ShipVia,ShipPostalCode asc

Result:

With SQL DISTINCT “order by” can be used for sorting using one or multiple columns. This is used in the above example queries.

Points to remember:

1. SQL Distinct statement returns distinct values for a given column.
2. SQL Distinct returns distinct combination of columns when used with multiple columns
3. SQL Distinct will return NULL values

More more on SQL Distinct statement, please see Using SQL Distinct clause in SQL Server.