How about simply:
select distinct c1, c2 from t
or
select c1, c2, count(*)
from t
group by c1, c2
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.
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.
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.
You can get result distinct by two columns use below SQL:
SELECT COUNT(*) FROM (SELECT DISTINCT c1, c2 FROM [TableEntity]) TE
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.
You can get result distinct by two columns use below SQL:
SELECT COUNT(*) FROM (SELECT DISTINCT c1, c2 FROM [TableEntity]) TE
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
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.
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
Source: Stackoverflow.com