In a lot of cases multiple rows in a table may contain the same values for a given subset of fields. The DISTINCT keyword is used to select distinct combinations of column values and eliminate duplicate rows from the default results of a SELECT statement.

By default SELECT statements returns all rows including duplicates if you do not specify DISTINCT. For example, here is the result of searching for all Cities in the Employee table without distinct. The query result will return a list of cities, including duplicates.

SELECT City
FROM Employees
ORDER BY City

In the next example we will apply the DISTINCT keyword to the same query and find all the distinct cities in which Northwind has employees.

SELECT DISTINCT City
FROM Employees
ORDER BY City

Eliminate Duplicate Query Results with Distinct

A database clothing apparel table might contain a Size field that identifies the size of a given apparel. It’s not unreasonable to assume that there may be multiple apparels with a Medium Size or even multiple apparels with the same combination of size and color.

Using DISTINCT with SELECT

The following example uses DISTINCT to generate a list of all unique titles in the DimEmployee table.

F

-- ALL and DISTINCT -- Implicit all SELECT City FROM SalesLT.Address; -- Explicit all SELECT ALL City FROM SalesLT.Address; -- Distinct SELECT DISTINCT City FROM SalesLT.Address; -- Distinct combination SELECT DISTINCT City, PostalCode FROM SalesLT.Address;

.

///////////////////New Copy Code ////////////////////////// ///////////////////////////////////////

.

Start Azure Data Studio, and create a new query (you can do this from the File menu or on the welcome page). In the new SQLQuery_… pane, use the Connect button to connect the query to the AdventureWorks saved connection. In the query editor, enter the following code: //////////////////Emeka mirror this with a different database////////////////////// SELECT Color FROM SalesLT.Product; Use the Run button to run the query, and and after a few seconds, review the results, which includes the color of each product in the table. Modify the query as follows, and re-run it. //////////////////////////////////////// SELECT ALL Color FROM SalesLT.Product; The results should be the same as before. The ALL parameter is the default behavior, and is applied implicitly to return a row for every record that meets the query criteria. Modify the query to replace ALL with DISTINCT, as shown here: //////////////////////////////////////// SELECT DISTINCT Color FROM SalesLT.Product; Run the modified query and note that the results include one row for each unique Color value. This ability to remove duplicates from the results can often be useful - for example to retrieve values in order to populate a drop-down list of color options in a user interface. Modify the query to add the Size field as shown here: //////////////////////////////////////// SELECT DISTINCT Color, Size FROM SalesLT.Product; Run the modified query and note that it returns each unique combination of color and size.

.

.

Related Content