Sybase Transact-SQL: Overview of the DISTINCT command

October 7, 2009

The DISTINCT command:

The DISTINCT command is used for retrieving unique data entries dependent on the columns specified. The opposite way of thinking about it is that it eliminates duplicate result rows. For a basic example lets assume we have the following person table and data

First Name	Last Name	City	State
Matthew	Denton	Fayetteville	New York
Elaine	Denton	Fayetteville	New York
Chris	Johnson	Syracuse	New York
Justin	Smith	Memphis	Texas
John	Craig	Orlando	Florida
Chris	Dean	New Orleans	Lousiana

With the above example we could use the distinct command to obtain all the states that people in the table are from.

SELECT DISTINCT State FROM PERSON

This will return

State
New York
Texas
Florida
Lousiana

where in not using the DISTINCT command would return New York three times. DISTINCT will work for all the columns that you are interacting with so if we modify the query to also return city in addition to the state the result would be:

City	State
Fayetteville	New York
Syracuse	New York
Memphis	Texas
Orlando	Florida
New Orleans	Lousiana

When using the distinct command it is important to know that if you use the ORDER BY command or GROUP BY command that even if you don’t select the columns used in those commands in the final results the values in their columns will be considered when determining distinct rows. So in our previous example if we order by First Name then we will get 3 New York results as the First Name for those data rows are not the same.

Resources

Sybase Manual – DISTINCT command

// T-SQL //

Next PostPrevious Post