CODEWITHSUNDEEP
mysqldatabase
Tomm
Tomm

Reputation: 2142

MySQL: Combine DISTINCT values from columns b & c GROUP BY column a

This is driving me batty. I've got the following basic table structure:

+-----+-----+-----+
| YEAR| ID1 | ID2 |
+-----+-----+-----+
| 2012| 103 | 105 |
| 2012| 110 | 114 |
| 2013| 103 | 110 |
| 2013| 114 | 120 |
| 2014| 122 | 103 |
+-----+-----+-----+

What I need is a list of distinct values from columns ID1 and ID2 grouped by results by column YEAR. To spice it up, the lowest YEAR must be shown per distinct value. Result table should look like this:

+-----+-----+
| Year|  ID |
+-----+-----+
| 2012| 103 |
| 2012| 105 |
| 2012| 110 |
| 2012| 114 |
| 2013| 120 |
| 2014| 122 |
+-----+-----+

Any help would be greatly appreciated!

Upvotes: 0

Views: 33

Answers (1)

jpw
jpw

Reputation: 44891

Use union all to build a relation (year, id) and apply min(year) to that and group by id:

select min(year) min_year, id
from (
    select year, id1 id from your_table
    union all
    select year, id2 id from your_table
) x
group by id

Upvotes: 3

Related Questions