86

Can you get the distinct combination of 2 different fields in a database table? if so, can you provide the SQL example.

Andy Lester
  • 91,102
  • 13
  • 100
  • 152
leora
  • 188,729
  • 360
  • 878
  • 1,366

5 Answers5

131

How about simply:

select distinct c1, c2 from t

or

select c1, c2, count(*)
from t
group by c1, c2
Howard Pinsley
  • 11,300
  • 15
  • 49
  • 66
  • Depending on data, group by is slower than distinct – noizer Jul 08 '15 at 08:27
  • 5
    For those seeing `Operand should contain 1 column(s)` error, your problem is that you are probably doing `select distinct(c1, c2) from t` and you are not allowed brackets here. That's how I got here. – BookOfGreg Mar 02 '17 at 13:04
17

If you want distinct values from only two fields, plus return other fields with them, then the other fields must have some kind of aggregation on them (sum, min, max, etc.), and the two columns you want distinct must appear in the group by clause. Otherwise, it's just as Decker says.

Jeffrey L Whitledge
  • 58,241
  • 9
  • 71
  • 99
8

You can get result distinct by two columns use below SQL:

SELECT COUNT(*) FROM (SELECT DISTINCT c1, c2 FROM [TableEntity]) TE
Wilson Wu
  • 1,790
  • 19
  • 13
6

If you still want to group only by one column (as I wanted) you can nest the query:

select c1, count(*) from (select distinct c1, c2 from t) group by c1
Denno
  • 61
  • 1
  • 1
  • I could not get this to work without using an alias: select a.c1, count(*) from (select distinct c1, c2 from t) a group by a.c1 – givonz Apr 19 '22 at 18:19
3

Share my stupid thought:

Maybe I can select distinct only on c1 but not on c2, so the syntax may be select ([distinct] col)+ where distinct is a qualifier for each column.

But after thought, I find that distinct on only one column is nonsense. Take the following relationship:

   | A | B
__________
  1| 1 | 2
  2| 1 | 1

If we select (distinct A), B, then what is the proper B for A = 1?

Thus, distinct is a qualifier for a statement.

youkaichao
  • 1,938
  • 1
  • 14
  • 26