Tuesday 26 July 2016

DISTINCT keyword



If you want to see repeated values only once in the result, then use DISTINCT keyword.

I am going to use following sample data.

CREATE TABLE employee(
  id int PRIMARY KEY,
  name VARCHAR(25),
  age int,
  salary DECIMAL(10, 4),
  mailId VARCHAR(25),
  city VARCHAR(10)
);

INSERT INTO employee VALUES (1, "Hari", 28, 12345.67, "hari@hari.com", "Bangalore");
INSERT INTO employee VALUES (2, "Sandesh", 30, 119345.00, "sandesh@sandesh.com", "Trivendram");
INSERT INTO employee VALUES (3, "Hari", 33, 12345.67, "phalgun@hari.com", "Hyderabad");
INSERT INTO employee VALUES (4, "Manju", 30, 87645.67, "manju@sandesh.com", "Bangalore");
INSERT INTO employee VALUES (5, "Rakesh", 26, 12345, "rakesh@hari.com", "Bangalore");
INSERT INTO employee VALUES (6, "Hari", 38, 87645.67, "sankalp@sankalp.com", "Chenai");
INSERT INTO employee VALUES (7, "Vadiraj", 28, 12345.67, "vadi@hari.com", "Bangalore");
INSERT INTO employee VALUES (8, "Sandesh", 28, 87645.67, "prasob@sandesh.com", "Trivendram");
INSERT INTO employee (id, name, salary) VALUES (9, "Kesav", 123457.89);
INSERT INTO employee VALUES (10, "Hari", 25, 198345.00, "hari@krishna.com", "Trivendram");
INSERT INTO employee VALUES (11, "Sandesh", 23, 119345.67, "phalgun@p.com", "Hyderabad");

mysql> SELECT * FROM employee;
+----+---------+------+-------------+---------------------+------------+
| id | name    | age  | salary      | mailId              | city       |
+----+---------+------+-------------+---------------------+------------+
|  1 | Hari    |   28 |  12345.6700 | hari@hari.com       | Bangalore  |
|  2 | Sandesh |   30 | 119345.0000 | sandesh@sandesh.com | Trivendram |
|  3 | Hari    |   33 |  12345.6700 | phalgun@hari.com    | Hyderabad  |
|  4 | Manju   |   30 |  87645.6700 | manju@sandesh.com   | Bangalore  |
|  5 | Rakesh  |   26 |  12345.0000 | rakesh@hari.com     | Bangalore  |
|  6 | Hari    |   38 |  87645.6700 | sankalp@sankalp.com | Chenai     |
|  7 | Vadiraj |   28 |  12345.6700 | vadi@hari.com       | Bangalore  |
|  8 | Sandesh |   28 |  87645.6700 | prasob@sandesh.com  | Trivendram |
|  9 | Kesav   | NULL | 123457.8900 | NULL                | NULL       |
| 10 | Hari    |   25 | 198345.0000 | hari@krishna.com    | Trivendram |
| 11 | Sandesh |   23 | 119345.6700 | phalgun@p.com       | Hyderabad  |
+----+---------+------+-------------+---------------------+------------+
11 rows in set (0.01 sec)


Get all names from employee table.

mysql> SELECT name FROM employee;
+---------+
| name    |
+---------+
| Hari    |
| Sandesh |
| Hari    |
| Manju   |
| Rakesh  |
| Hari    |
| Vadiraj |
| Sandesh |
| Kesav   |
| Hari    |
| Sandesh |
+---------+
11 rows in set (0.00 sec)


Get distinct names from employee table

mysql> SELECT DISTINCT name FROM employee;
+---------+ 
| name    |
+---------+
| Hari    |
| Sandesh |
| Manju   |
| Rakesh  |
| Vadiraj |
| Kesav   |
+---------+
6 rows in set (0.00 sec)


Get all names and salaries from employee.

mysql> SELECT name, salary FROM employee;
+---------+-------------+
| name    | salary      |
+---------+-------------+
| Hari    |  12345.6700 |
| Sandesh | 119345.0000 |
| Hari    |  12345.6700 |
| Manju   |  87645.6700 |
| Rakesh  |  12345.0000 |
| Hari    |  87645.6700 |
| Vadiraj |  12345.6700 |
| Sandesh |  87645.6700 |
| Kesav   | 123457.8900 |
| Hari    | 198345.0000 |
| Sandesh | 119345.6700 |
+---------+-------------+
11 rows in set (0.00 sec)


Get all distinct names and salaries from employee table

mysql> SELECT DISTINCT name, salary FROM employee;
+---------+-------------+
| name    | salary      |
+---------+-------------+
| Hari    |  12345.6700 |
| Sandesh | 119345.0000 |
| Manju   |  87645.6700 |
| Rakesh  |  12345.0000 |
| Hari    |  87645.6700 |
| Vadiraj |  12345.6700 |
| Sandesh |  87645.6700 |
| Kesav   | 123457.8900 |
| Hari    | 198345.0000 |
| Sandesh | 119345.6700 |
+---------+-------------+
10 rows in set (0.00 sec)


As you see, one row with record ‘| Hari    |  12345.6700 |’ removed.







Previous                                                 Next                                                 Home

No comments:

Post a Comment