SQL Server DISTINCT function

DISTINCT : Returns a one-column table that contains the distinct values from the specified column. DISTINCT is an optional keyword that precedes the list of columns specified in the SELECT clause.

The SQL DISTINCT clause is used to remove duplicates from the result set of a SELECT statement.

Note:
1. When only one expression is provided in the DISTINCT clause, the query will return the unique values for that expression.
2. When more than one expression is provided in the DISTINCT clause, the query will retrieve unique combinations for the expressions listed.

3. In SQL, the DISTINCT clause doesn't ignore NULL values. So when using the DISTINCT clause in your SQL statement, your result set will include NULL as a distinct value.

Example : SELECT with DISTINCT on two columns

SELECT  DISTINCT agent_code,ord_amount FROM orders WHERE agent_code='A002';

Example : SELECT with DISTINCT on three columns

SELECT  DISTINCT agent_code, ord_amount,cust_code FROM orders WHERE agent_code='A002';

Example : SELECT with DISTINCT on all columns of the first query

SELECT DISTINCT agent_code,ord_amount,cust_code,ord_num FROM orders WHERE agent_code='A002';

Example : SELECT with DISTINCT on multiple columns and ORDER BY clause

SELECT  DISTINCT agent_code,ord_amount FROM orders WHERE agent_code='A002'ORDER BY ord_amount;

Example : COUNT() function and SELECT with DISTINCT on multiple columns.

You can use count() function in a select statement with distinct on multiple columns to count the distinct rows. Here is an example :

SELECT COUNT(*)FROM (SELECT  DISTINCT agent_code, ord_amount,cust_code FROM orders WHERE agent_code='A002');