Distinct with two columns : DISTINCT « Query « SQL Server / T-SQL Tutorial






  1. SQL Server / T-SQL Tutorial
  2. Query
  3. DISTINCT
3> IF OBJECT_ID('dbo.Sessions') IS NOT NULL
4>   DROP TABLE dbo.Sessions;
5> GO
1>
2> CREATE TABLE dbo.Sessions
3> (
4>   keycol    INT         NOT NULL IDENTITY PRIMARY KEY,
5>   app       VARCHAR(10) NOT NULL,
6>   usr       VARCHAR(10) NOT NULL,
7>   starttime DATETIME    NOT NULL,
8>   endtime   DATETIME    NOT NULL,
9>   CHECK(endtime > starttime)
10> );
11> GO
1>
2> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user1', '20060212 08:30', '20060212 10:30');
3> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user2', '20060212 08:30', '20060212 08:45');
4> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user1', '20060212 09:00', '20060212 09:30');
5> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user2', '20060212 09:15', '20060212 10:30');
6> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user1', '20060212 09:15', '20060212 09:30');
7> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user2', '20060212 10:30', '20060212 14:30');
8> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user1', '20060212 10:45', '20060212 11:30');
9> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app1', 'user2', '20060212 11:00', '20060212 12:30');
10> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user1', '20060212 08:30', '20060212 08:45');
11> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user2', '20060212 09:00', '20060212 09:30');
12> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user1', '20060212 11:45', '20060212 12:00');
13> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user2', '20060212 12:30', '20060212 14:00');
14> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user1', '20060212 12:45', '20060212 13:30');
15> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user2', '20060212 13:00', '20060212 14:00');
16> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user1', '20060212 14:00', '20060212 16:30');
17> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES('app2', 'user2', '20060212 15:30', '20060212 17:00');
18> GO
1>
2> SELECT DISTINCT app, starttime AS ts
3> FROM dbo.Sessions;
4> GO
app        ts
---------- -----------------------
app1       2006-02-12 08:30:00.000
app1       2006-02-12 09:00:00.000
app1       2006-02-12 09:15:00.000
app1       2006-02-12 10:30:00.000
app1       2006-02-12 10:45:00.000
app1       2006-02-12 11:00:00.000
app2       2006-02-12 08:30:00.000
app2       2006-02-12 09:00:00.000
app2       2006-02-12 11:45:00.000
app2       2006-02-12 12:30:00.000
app2       2006-02-12 12:45:00.000
app2       2006-02-12 13:00:00.000
app2       2006-02-12 14:00:00.000
app2       2006-02-12 15:30:00.000
1>
2> drop table sessions;
3> GO
1>








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