CODEWITHSUNDEEP
mysqlsqldistinct
Seungho Lee
Seungho Lee

Reputation: 1155

Retrieving two columns with 'DISTINCT' applied on each column in MySQL

This is a table books

+--------------+--------------+
| author_fname | author_lname |
+--------------+--------------+
| Dan          | Harris       |
| Freida       | Harris       |
| George       | Saunders     |
+--------------+--------------+

I am aware that if DISTINCT is used as

SELECT DISTINCT author_fname, author_lname FROM books;

This will give me the below result because it will give me distinct rows combined by author_fname and author_lname:

+--------------+--------------+
| author_fname | author_lname |
+--------------+--------------+
| Dan          | Harris       |
| Freida       | Harris       |
| George       | Saunders     |
+--------------+--------------+

Is there any way to apply DISTINCT to each column author_fname and author_lname like below result so that I only get Harris and Saunders for author_lname other than applying DISTINCT to a column-wise?

+--------------+--------------+
| author_fname | author_lname |
+--------------+--------------+
| Dan          | Harris       |
| Freida       | Saunders     |
| George       |    sth       |
+--------------+--------------+

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you want one row per distinct last name, you can use group by:

select last_name, min(first_name)
from books
group by last_name;

Upvotes: 1

Related Questions