Distinct on two columns

Question

How to select unique results from both columns:

+---------+--------------+
| team_id | opponents_id |
+---------+--------------+
|       1 |            2 |
|       1 |            3 |
|       1 |            4 |
|       1 |            5 |
|       1 |            6 |
|       2 |            1 |
|       2 |            3 |
|       2 |            4 |
|       2 |            5 |
|       2 |            6 |
|       3 |            1 |
|       3 |            2 |
|       3 |            4 |
|       3 |            5 |
|       3 |            6 |
|       4 |            1 |
|       4 |            2 |
|       4 |            3 |
|       4 |            5 |
|       4 |            6 |
|       5 |            1 |
|       5 |            2 |
|       5 |            3 |
|       5 |            4 |
|       5 |            6 |
|       6 |            1 |
|       6 |            2 |
|       6 |            3 |
|       6 |            4 |
|       6 |            5 |
+---------+--------------+

What do you class as 'unique'? For example, is `1-3` the same as `3-1`? — BenM, Jun 06 '17 at 16:06
The rows you posted are already distinct. What is the outcome you expect? — axiac, Jun 06 '17 at 16:09
I need it to take an unique team_id and an random opponents_id. For example: 1 2 - 2 4 - 3 6 - 4 5 - 5 1 - 6 3 — shampally, Jun 06 '17 at 17:28

score 2 · Answer 1 · answered Jun 06 '17 at 16:08

2

Just use distinct

 select distinct team_id,opponents_id
   from your_table

answered Jun 06 '17 at 16:08

Utsav

7,914
2
17
38

score 1 · Answer 2 · answered Jun 06 '17 at 16:08

1

If you want to suppress duplicate based on the two column then you should group your value with composite index column1, column2

SELECT column1, column2
FROM MA_TABLE
GROUP BY column1, column2

It should return you a list of unique resultset

answered Jun 06 '17 at 16:08

fxlacroix

557
4
18

`SELECT DISTINCT column1, column2` produces the same result but it is much faster. – axiac Jun 06 '17 at 16:09
@axiac do you have a article for that? what I've read is in most engines they produce similar/same execution plans. https://stackoverflow.com/questions/581521/whats-faster-select-distinct-or-group-by-in-mysql Nevermind found one: http://charlesnagy.info/it/mysql/difference-between-distinct-and-group-by – xQbert Jun 06 '17 at 17:24
It still doesn't work the way I need it to work: http://puu.sh/wAOva/8befe52df2.png And when I group by team_id, the Opponents_id are all random and not unique.. http://puu.sh/wAODG/62d98d3ea8.png – shampally Jul 03 '17 at 20:48

Distinct on two columns

2 Answers2