CODEWITHSUNDEEP
sql-server
limit
limit

Reputation: 31

SELECT rows with unique values in two columns

I have a table like this:

id          col1 col2        
----------- ---- ----------- 
1           A    5
2           A    6
3           B    5
4           B    6
5           B    7
6           C    6
7           D    7

I need to select no more than one row with any exact value of col1 or col2. For example one possible solution is:

id          col1 col2        
----------- ---- ----------- 
1           A    5
4           B    6
7           D    7

Other possible solution is:

id          col1 col2        
----------- ---- ----------- 
3           B    5
6           C    6
7           D    7

I need to find one of any possible solutions.

The algorithm is very simple:

  1. Get any row from table to an output.
  2. Exclude all rows from the table that have value in col1 equal to value of col1 or value in col2 equal to value of col2 in the selected row.
  3. If table is not empty go to step 1.

How to implement this logic in SQL SELECT statement?

Code to generate test table:

CREATE TABLE #t (id INT IDENTITY, col1 CHAR(1), col2 INT) ;

INSERT  INTO #t (col1, col2)
VALUES  
    ('A', 5),
    ('A', 6),
    ('B', 5),
    ('B', 6),
    ('B', 7),
    ('C', 6),
    ('D', 7);

Upvotes: 1

Views: 1543

Answers (4)

Shanmugaprabu Natarajan
Shanmugaprabu Natarajan

Reputation: 1

Try this query:

SELECT  id, col1, col2
FROM ( SELECT    * ,
       RANK() OVER ( PARTITION BY col2 ORDER BY col2 ) Row#
       FROM      #t
     ) x
WHERE   x.Row# = 1

Upvotes: 0

sacse
sacse

Reputation: 3744

try the following using cursor:

create table #final (id int, col1 varchar(10), col2 int)

declare @id int, @col1 varchar(10), @col2 int
declare cur cursor for select id, col1, col2 from #t order by newid()

open cur
fetch next from cur into @id, @col1, @col2
while @@FETCH_STATUS = 0
begin
    if (@col1 in (select col1 from #final) or @col2 in (select col2 from #final))
    begin
        fetch next from cur into @id, @col1, @col2
        continue
    end
    insert into #final
    select id, @col1, @col2 from #t where col1 = @col1 and col2 = @col2 and id = @id
    fetch next from cur into @id, @col1, @col2
end

close cur
deallocate cur

select * from #final order by id

drop table #final

Result:

Run_result

Upvotes: 0

JNevill
JNevill

Reputation: 50034

This is not an easy one to solve through straight SQL. I figured I'd give it a crack using a recursive CTE. This spits out all possible combinations, but... in one field called "path" here. Perhaps it will present a way forward using straight tsql.

With recCTE AS
(
    SELECT 
      col1, 
      col2, 
      0 as depth, 
      CAST(col1 + '|' + CAST(col2 AS varchar(10)) AS VARCHAR(30)) as path 
    FROM t

    UNION ALL

    SELECT       
      t1.col1, 
      t1.col2, 
      rec.depth + 1, 
      CAST(rec.path + '>' + t1.col1 + '|' + cast(t1.col2 as varchar(10)) as varchar(30))
    FROM t t1
        INNER JOIN recCTE rec
        ON rec.path NOT LIKE '%|' + CAST(t1.col2 as varchar(10)) + '%'
           AND rec.path NOT LIKE '%' + CAST(t1.col2 as varchar(10)) + '|%'
    WHERE depth + 1 <= 3 

)
SELECT * 
FROM recCTE 
WHERE depth = 2

Upvotes: 0

Baral
Baral

Reputation: 3141

SELECT  id, col1, col2
FROM    ( SELECT    * ,
                    ROW_NUMBER() OVER ( PARTITION BY col2 ORDER BY col2 ) Row#
          FROM      #t
        ) x
WHERE   x.Row# = 1

Upvotes: 2

Related Questions