The SQL key word DISTINCT has the effect that equivalent data records are output only once. : Distinct « Select Clause « SQL / MySQL






The SQL key word DISTINCT has the effect that equivalent data records are output only once.

       
mysql>
mysql>
mysql> CREATE TABLE publishers (
    ->   publID int(11) NOT NULL auto_increment,
    ->   publName varchar(60) collate latin1_german1_ci NOT NULL default '',
    ->   ts timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    ->   PRIMARY KEY  (publID),
    ->   KEY publName (publName)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql> INSERT INTO publishers VALUES (1,'A','2004-12-02 18:36:58'),
    ->                                 (2,'Apress','2004-12-02 18:36:58'),
    ->                                 (3,'New Riders','2004-12-02 18:36:58'),
    ->                                 (4,'O\'Reilly & Associates','2004-12-02 18:36:58'),
    ->                                 (5,'Hanser','2004-12-02 18:36:58');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> CREATE TABLE titles (
    ->   titleID int(11),
    ->   title varchar(100),
    ->   subtitle varchar(100),
    ->   edition tinyint(4),
    ->   publID int(11),
    ->   catID int(11),
    ->   langID int(11),
    ->   year int(11),
    ->   isbn varchar(20),
    ->   comment varchar(255),
    ->   ts timestamp,
    ->   authors varchar(255),
    ->   PRIMARY KEY  (titleID)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql>
mysql> INSERT INTO titles VALUES (1,'Linux','Installation',5,1,57,2,2000,NULL,NULL,'2005-02-28 13:34:21','Michael'),
    ->                           (2,'Excel',NULL,NULL,2,3,NULL,2000,NULL,NULL,'2005-02-28 13:34:22','David'),
    ->                           (3,'XML',NULL,NULL,1,2,NULL,1997,NULL,NULL,'2005-02-28 13:34:22','Edwards'),
    ->                           (4,'PHP',NULL,NULL,3,6,NULL,2000,NULL,NULL,'2005-02-28 13:34:22','Tom'),
    ->                           (5,'MySQL','',0,3,34,NULL,2000,'','','2005-02-28 13:34:22','Paul'),
    ->                           (6,'Java',NULL,NULL,4,34,NULL,1999,NULL,NULL,'2005-02-28 13:34:22','Tim');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TABLE rel_title_author (
    ->   titleID int(11),
    ->   authID int(11),
    ->   ts timestamp ,
    ->   PRIMARY KEY  (titleID,authID)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql>
mysql> INSERT INTO rel_title_author VALUES (1,1,'2004-12-02 18:37:09'),
    ->                                       (2,1,'2004-12-02 18:37:09'),
    ->                                       (2,2,'2004-12-02 18:37:09'),
    ->                                       (3,3,'2005-02-24 10:46:37'),
    ->                                       (3,4,'2005-02-24 10:46:37'),
    ->                                       (3,5,'2005-02-24 10:46:37'),
    ->                                       (4,6,'2004-12-02 18:37:09');
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> CREATE TABLE authors (
    ->   authID int(11) NOT NULL auto_increment PRIMARY KEY ,
    ->   authName varchar(60),
    ->   ts timestamp
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql>
mysql>
mysql> INSERT INTO authors VALUES (1,'Tom','2004-12-02 18:36:51'),
    ->                              (2,'Jack','2004-12-02 18:36:51'),
    ->                              (3,'Jane','2004-12-02 18:36:51'),
    ->                              (4,'Amy','2004-12-02 18:36:51'),
    ->                              (5,'Bob','2004-12-02 18:36:51'),
    ->                              (6,'Race','2004-12-02 18:36:51'),
    ->                              (7,'Green','2004-12-02 18:36:51');
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql>
mysql> SELECT DISTINCT publName, authName
    -> FROM publishers, titles, rel_title_author, authors
    -> WHERE titles.titleID = rel_title_author.titleID
    -> AND authors.authID = rel_title_author.authID
    -> AND publishers.publID = titles.publID
    -> ORDER BY publName, authName;
+------------+----------+
| publName   | authName |
+------------+----------+
| A          | Amy      |
| A          | Bob      |
| A          | Jane     |
| A          | Tom      |
| Apress     | Jack     |
| Apress     | Tom      |
| New Riders | Race     |
+------------+----------+
7 rows in set (0.00 sec)

mysql>
mysql>
mysql> drop table publishers;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table titles;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table rel_title_author;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table authors;
Query OK, 0 rows affected (0.00 sec)

   
    
    
    
    
    
    
  








Related examples in the same category

1.Use DISTINCT to get non-dupliate records
2.Use DISTICNT to get unique value
3.Eliminating Duplicate Data Using DISTINCT 1
4.Eliminating Duplicate Data Using DISTINCT 2
5.Select distinct records using JOIN
6.DISTINCT works with multiple-column output too.
7.how many different drivers there are, use COUNT(DISTINCT)
8.DISTINCT with two columns
9.DISTINCT works with expressions, not just column values.
10.Inserting name values into the multisequence table generates separate sequences for each distinct name:
11.Count distinct
12.Distinct sub string
13.Average distinct value
14.Return a list of surnames, with each surname appearing only once?
15.The fraction of the records that contain unique or non-unique names: