34

I have a mysql table that looks like this:

1   value1    value2    3534
2   value1    value1    8456
3   value1    value2    3566
4   value1    value3    7345
5   value2    value3    6734

I need a query to select all the rows with distinct column 2 and 3, for example the output I want for this example will look like this:

1   value1    value2    3534
2   value1    value1    8456
4   value1    value3    7345
5   value2    value3    6734

i've found a few samples on how to do it but they all select distinct on each column individually.

James Harzs
  • 1,853
  • 5
  • 21
  • 30

6 Answers6

42

Update 1

Better you use this against above.

SELECT id, col2, col3, col4
FROM yourtable
GROUP BY col2, col3;

Demo

The reason I am saying is because using CONCAT, I am not getting desired result in this case. First query is returning me 5 rows however CONCAT is returning me 4 rows which is INCORRECT.

Hope you got my point.

Assumed the columns in the table are (id, col2, col3, col4).

SELECT DISTINCT(CONCAT(col2, col3)) as "dummy column", id, col2, col3, col4
FROM yourtable
GROUP BY CONCAT(col2, col3);

OR

SELECT id, col2, col3, MIN(col4)
FROM yourtable
GROUP BY col2, col3;

live working example

Fahim Parkar
  • 30,974
  • 45
  • 160
  • 276
23

Assuming that the first column is unique, you can do this:

SELECT id, col2, col3, col4
FROM yourtable
WHERE id IN
(
    SELECT MIN(id)
    FROM yourtable
    GROUP BY col2, col3
)

See it working online: sqlfiddle

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
1

Assuming the columns in the table are (id, col1, col2, col3), you could:

SELECT  *
FROM    YourTable yt
JOIN    (
        SELECT  MIN(id) as minid
        FROM    YourTable
        GROUP BY
                col1, col2
        ) filter
ON      filter.minid = yt.id
todgru
  • 139
  • 2
  • 12
Andomar
  • 232,371
  • 49
  • 380
  • 404
0

This query makes sure that the combination of column1 and column2 is unique, while selecting the minimum value of column three

SELECT col1, col2, MIN(col3)
FROM yourTable
GROUP BY col1, col2
schadr
  • 398
  • 2
  • 10
0

THe simplest query for this is 

SELECT col1, col2, MIN(col3)
FROM myTable
GROUP BY col1, col2
Sashi Kant
  • 13,277
  • 9
  • 44
  • 71
0

Using the group by method is returning me extra rows, where as explicitly checking each field although longer returns the same no of records as count(Distinct ..)

SELECT id, col2, col3, col4
FROM yourtable yt
WHERE id =
(
 SELECT MIN(id)
 FROM yourtable yt1
 WHERE yt.col2 = yt1.col2
 AND yt.col3 = yt1.col3
)
Derya
  • 11
  • 1
  • Would be useful if you showed test data where this gives a different answer than the accepted answer. AFAIK, the only reason it would be different is when there are `NULL` values. `DISTINCT` allows columns with `NULL`, but `group by` omits rows with such columns. – ToolmakerSteve Jun 16 '21 at 14:55