SQL No Dup (Distinct) in rows, but ignore single column

Question

For example, a table with three columns and data

col1 col2 col3 10 20 30 40 50 60 40 50 80 

You want to make a choice where only one is returned for the last two lines, since col1 and col2 match.

select distinct will not work as col3 are different.

so the output will be

  10 20 30 40 50 xx (don't care) 

Answer 1

Since you have only one additional column, you can simply use an arbitrary MIN / MAX aggregate and GROUP BY

 SELECT col1, col2, MAX(col3) AS col3 FROM YourTable GROUP BY col1, col2 

In general, if your RDBMS supports analytic functions, you can use

 WITH T AS (SELECT col1, col2, col3, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col1, col2) AS RN FROM YourTable) SELECT col1, col2, col3 FROM T WHERE RN = 1 

Answer 2

I did this in SQL Server:

 -- Setup test data: declare @table table ( col1 int, col2 int, col3 int ) insert into @table values (10, 20, 30) insert into @table values (40, 50, 60) insert into @table values (40, 50, 80) -- Here the query: select col1, col2, cast(min(col3) as varchar(10)) as col3 from @table group by col1, col2 having count(*) = 1 union all select col1, col2, 'xx' as col3 from @table group by col1, col2 having count(*) > 1 

I assume this assumes that you do not have duplicate rows (where all fields are duplicates), otherwise you may have the wrong "xx".