Advertisement
Advertisement


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.

2018/12/24
1
194
12/24/2018 5:30:40 AM

Accepted Answer

try this query

 SELECT ID, FirstName, LastName FROM table GROUP BY(FirstName)
2014/09/04
193
9/4/2014 5:08:59 AM

The DISTINCT keyword doesn't really work the way you're expecting it to. When you use SELECT DISTINCT col1, col2, col3 you are in fact selecting all unique {col1, col2, col3} tuples.

2011/05/11

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.

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.


Exclusion Join - Ambiguous Entities

If the first and last name combination are not unique (ambiguous) and you have multiple rows of the same values, you can filter the result set by including an OR condition on the JOIN criteria to also filter by id.

See Demonstration

table_name data

(1, 'John', 'Doe'),
(2, 'Bugs', 'Bunny'),
(3, 'John', 'Johnson'),
(4, 'John', 'Doe'),
(5, 'John', 'Johnson')

Query

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

Result

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

Ordered Subquery

EDIT

My original answer using an ordered subquery, was written prior to MySQL 5.7.5, which is no longer applicable, due to the changes with ONLY_FULL_GROUP_BY. Please use the exclusion join examples above instead.

It is also important to note; when ONLY_FULL_GROUP_BY is disabled (original behavior prior to MySQL 5.7.5), the use of GROUP BY without an aggregate function may yield unexpected results, because MySQL is free to choose ANY value within the data set being grouped [sic].

Meaning an ID or lastname value may be retrieved that is not associated with the retrieved firstname row.


WARNING

With MySQL GROUP BY may not yield the expected results when used with ORDER BY

See Test Case Example

The best method of implementation, to ensure expected results, is to filter the result set scope using an ordered subquery.

table_name data

(1, 'John', 'Doe'),
(2, 'Bugs', 'Bunny'),
(3, 'John', 'Johnson')

Query

SELECT * FROM (
    SELECT * FROM table_name ORDER BY ID DESC
) AS t1
GROUP BY FirstName

Result

| ID | first |    last |
|----|-------|---------|
|  2 |  Bugs |   Bunny |
|  3 |  John | Johnson |

Comparison

To demonstrate the unexpected results when using GROUP BY in combination with ORDER BY

Query

SELECT * FROM table_name GROUP BY FirstName ORDER BY ID DESC

Result

| ID | first |  last |
|----|-------|-------|
|  2 |  Bugs | Bunny |
|  1 |  John |   Doe |
2020/05/18

SELECT ID,LastName 
From TABLE_NAME 
GROUP BY FirstName 
HAVING COUNT(*) >=1
2012/05/03

SELECT firstName, ID, LastName from tableName GROUP BY firstName
2013/02/14

How about

`SELECT 
    my_distinct_column,
    max(col1),
    max(col2),
    max(col3)
    ...
 FROM
    my_table 
 GROUP BY 
    my_distinct_column`
2018/12/02

Source: https://stackoverflow.com/questions/5967130
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]