CODEWITHSUNDEEP
mysqldistinct
Tom
Tom

Reputation: 7586

Select distinct rows by multiple columns and retrieve yet another column which is not taking part in determining the distinctness

So here's how I select distinct rows by a combination of multiple columns (a, b and c):

select distinct a,b,c from my_table

This is good, but I need yet another column retrieved for these rows (d) which I can't add to the select part, because then it also plays a role in determining row uniqueness which I don't want.

How can I retrieve an additional column without it affecting row uniqueness?

Upvotes: 3

Views: 835

Answers (2)

KRyan
KRyan

Reputation: 7598

Looks like a job for a join... probably a LEFT JOIN. Something like this:

SELECT DISTINCT L.a, L.b, L.c FROM `my_table` L
LEFT JOIN (
    SELECT a, b, c, d FROM `my_table`
) R ON L.a=R.a AND L.b=R.b AND L.c=R.c

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can do this using a group by. In MySQL, you can do:

select a, b, c, d
from my_table
group by a, b, c

This chooses an arbitrary value for "d", which would typically (but not guanteed!) be the first value encountered. This uses a feature of MySQL called Hidden Columns.

For code that works in MySQL and other databases, you need to be more explicit:

select a, b, c, min(d)
from my_table
group by a, b, c

Getting an actual random value for d in MySQL is a bit trickier and requires more work. Here is one way:

select distinct a, b, c,
       (select d from my_table mt2
        where mt.a = mt2.a and mt.b = mt2.b and mt.c = mt2.c
        order by rand()
        limit 1
       ) d
from my_table mt

Upvotes: 2

Related Questions