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

Question

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?

Answer 1

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