Mysql Select One Column Distinct, with Corresponding Other Columns

Question

ID   FirstName   LastName
1      John        Doe
2      Bugs        Bunny
3      John        Johnson

I want to select DISTINCT results from the FirstName column, but I need the corresponding ID and LastName.

The result set needs to show only one John, but with an ID of 1 and a LastName of Doe.

Accepted Answer

try this query

 SELECT ID, FirstName, LastName FROM table GROUP BY(FirstName)

Answer 2

To avoid potentially unexpected results when using GROUP BY without an aggregate function, as is used in the accepted answer, because MySQL is free to retrieve ANY value within the data set being grouped when not using an aggregate function [sic] and issues with ONLY_FULL_GROUP_BY. Please consider using an exclusion join.

Must Read

Exclusion Join - Unambiguous Entities

Assuming the firstname and lastname are uniquely indexed (unambiguous), an alternative to GROUP BY is to sort using a LEFT JOIN to filter the result set, otherwise known as an exclusion JOIN.

See Demonstration

Ascending order (A-Z)

To retrieve the distinct firstname ordered by lastname from A-Z

Query

SELECT t1.*
FROM table_name AS t1
LEFT JOIN table_name AS t2
ON t1.firstname = t2.firstname
AND t1.lastname > t2.lastname
WHERE t2.id IS NULL;

Result

| id | firstname | lastname |
|----|-----------|----------|
|  2 |      Bugs |    Bunny |
|  1 |      John |      Doe |

Descending order (Z-A)

To retrieve the distinct firstname ordered by lastname from Z-A

Query

SELECT t1.*
FROM table_name AS t1
LEFT JOIN table_name AS t2
ON t1.firstname = t2.firstname
AND t1.lastname < t2.lastname
WHERE t2.id IS NULL;

Result

| id | firstname | lastname |
|----|-----------|----------|
|  2 |      Bugs |    Bunny |
|  3 |      John |  Johnson |

You can then order the resulting data as desired.

Mysql Select One Column Distinct, with Corresponding Other Columns

12 Answers

Must Read

Exclusion Join - Unambiguous Entities

Chloe Bennett