SQL Server DISTINCT Clause

If the column constraint is not set to primary or with uniqueness, a column in a table may contain numerous duplicate entries. In these situations, we use DISTINCT in the SELECT statement to extract the unique data. It won’t actually remove data from the table; it will merely display the result-set.

SELECT DISTINCT column_name FROM table name;
SELECT DISTINCT column_name1, column_name2,… FROM table name;

The DISTINCT clause in SQL can also be used on several columns. Rows with identical values in all of the chosen fields will be removed from the output using DISTINCT. In other words, the query will return unique combinations for the expressions listed when the DISTINCT clause contains several expressions.

Apart from that, the DISTINCT clause in SQL Server does not disregard NULL values. As a result, if you use the Separate clause in your SQL query, NULL will be included in your result set as a distinct value.

The DISTINCT clause can be used along with the aggregate function such as COUNT.

However, it cannot be used for multiple columns. For that, either inline select statement/sub-query or a temp table to be used.

Some examples:
SELECT DISTINCT(StudentName) FROM [tbStudentDetails]
SELECT DISTINCT StudentName, GradeID FROM [tbStudentDetails]
SELECT COUNT(*) FROM (SELECT DISTINCT StudentName, GradeID FROM tbStudentDetails) x


One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s