Find all users with a unique last name in MySQL?

MySQLMySQLi Database

To find all users with unique last name, use GROUP BY HAVING COUNT().

Let us create a table −

Example

mysql> create table demo76
   -> (
   -> firstName varchar(20),
   -> lastName varchar(20)
   -> );
Query OK, 0 rows affected (9.29

Insert some records into the table with the help of insert command −

Example

mysql> insert into demo76 values('John','Doe');
Query OK, 1 row affected (2.52

mysql> insert into demo76 values('David','Smith');
Query OK, 1 row affected (6.31

mysql> insert into demo76 values('Adam','Smith');
Query OK, 1 row affected (1.52

Display records from the table using select statement −

Example

mysql> select *from demo76;

This will produce the following output −

Output

+-----------+----------+

| firstName | lastName |

+-----------+----------+

| John      | Doe      |

| David     | Smith    |

| Adam      | Smith    |

+-----------+----------+

3 rows in set (0.00 sec)

Following is the query to find all users with a unique last name −

Example

mysql> select max(tbl.firstName), tbl.lastName,
   -> count(distinct tbl.firstName) as CountOfFirstName
   -> from demo76 as tbl
   -> group by tbl.lastName
   -> having count(distinct tbl.firstName) = 1;

This will produce the following output −

Output

+--------------------+----------+------------------+

| max(tbl.firstName) | lastName | CountOfFirstName |

+--------------------+----------+------------------+

| John               | Doe      |                1 |

+--------------------+----------+------------------+

1 row in set (0.00 sec)
raja
Published on 11-Dec-2020 10:00:20
Advertisements