A SELECT statement that eliminates duplicate rows : DISTINCT « Query « SQL Server / T-SQL Tutorial






DISTINCT keyword prevents duplicate (identical) rows from being included in the result set.
It also causes the result set to be sorted by its first column.
To use the DISTINCT keyword, code it immediately after the SELECT keyword.


13>
14>
15> create table Bankers(
16>    BankerID             Integer,
17>    BankerName           VARCHAR(20),
18>    BankerContactLName   VARCHAR(20),
19>    BankerContactFName   VARCHAR(20),
20>    BankerCity           VARCHAR(20),
21>    BankerState          VARCHAR(20),
22>    BankerZipCode        VARCHAR(20),
23>    BankerPhone          VARCHAR(20)
24> )
25> GO
1>
2> insert into Bankers values (1, 'ABC Inc.','Joe','Smith','Vancouver','BC','11111','111-111-1111');
3> GO

(1 rows affected)
1> insert into Bankers values (2, 'DEF Inc.','Red','Rice', 'New York', 'DE','22222','222-222-2222');
2> GO

(1 rows affected)
1> insert into Bankers values (3, 'HJI Inc.','Kit','Cat',  'Paris',    'CA','33333','333-333-3333');
2> GO

(1 rows affected)
1> insert into Bankers values (4, 'QWE Inc.','Git','Black','Regina',   'ER','44444','444-444-4444');
2> GO

(1 rows affected)
1> insert into Bankers values (5, 'RTY Inc.','Wil','Lee',  'Toronto',  'YU','55555','555-555-5555');
2> GO

(1 rows affected)
1> insert into Bankers values (6, 'YUI Inc.','Ted','Larry','Calgary',  'TY','66666','666-666-6666');
2> GO

(1 rows affected)
1> insert into Bankers values (7, 'OIP Inc.','Yam','Act',  'San Franc','FG','77777','777-777-7777');
2> GO

(1 rows affected)
1> insert into Bankers values (8, 'SAD Inc.','Hit','Eat',  'Orland',   'PO','88888','888-888-8888');
2> GO

(1 rows affected)
1> insert into Bankers values (9, 'DFG Inc.','Sad','Lee',  'Wisler',   'PL','99999','999-999-9999');
2> GO

(1 rows affected)
1> insert into Bankers values (0, 'GHJ Inc.','Bit','Lee',  'Ticker',   'MN','00000','000-000-0000');
2> GO

(1 rows affected)
1>
2>
3> SELECT DISTINCT BankerCity, BankerState
4> FROM Bankers
5> GO
BankerCity           BankerState
-------------------- --------------------
Calgary              TY
New York             DE
Orland               PO
Paris                CA
Regina               ER
San Franc            FG
Ticker               MN
Toronto              YU
Vancouver            BC
Wisler               PL

(10 rows affected)
1>
2> drop table Bankers;
3> GO
1>
2>








1.12.DISTINCT
1.12.1.A SELECT statement that eliminates duplicate rows
1.12.2.A summary query that uses the DISTINCT keyword
1.12.3.When the DISTINCT keyword is used, all duplicate values are eliminated before the function COUNT is applied.
1.12.4.Distinct with two columns
1.12.5.DISTINCT can be used only once in a SELECT list, and it must precede all column names in that list.
1.12.6.Correlated subquery using Distinct
1.12.7.Using SELECT DISTINCT or a GROUP BY statement in the inner query