SQL special select from table

2
votes

I have a table with about 20 columns and 2000 rows. Example:

Col1   Col2  Col3  Col4   ...
A01    22     AB   11
A01    22     AX   112
A01    23     A5   11
A02    20     AB   AA
A04    21     AB   11
A04    21     AU   11
A04    29     AB   BA
A05    21     AB   11
AAA    111    XX   18
AAA    222    GT   1O 
...

I need a select which displays all rows and all columns that satisfy the requirement of two columns (Col1 and Col2) based on the following:

if Col1 is unique - show row, or if Col1 is not unique show all row only if Col1 and Col2 are same. From the previos table is after select the result:

Col1   Col2  Col3  Col4   ...
A01    22     AB   11
A01    22     AX   112
A02    20     AB   AA
A04    21     AB   11
A04    21     AU   11
A05    21     AB   11
AAA    111    XX   18
...

The new table (your solution) contains data: Col1 Col2 Col3 Col4 ...

A01 22 AB 11

A01 22 AX 112

A02 20 AB AA

A04 21 AB 11

A04 21 AU 11

A05 21 AB 11

AAA 111 XX 18

...

what I wont see from this is:

Col1 Col2 Col3 Col4 ...

A01 2 AB 11

A02 1 AB AA

A04 2 AB 11

A05 1 AB 11

AAA 1 XX 18

...

2
sql
Finally, wich RDBMS are you using? You have one correct answer that works for Oracle but seems that you need a MS SQL Server answer – Yaroslav
@Yaroslav: My answer works for both Oracle and MS SQL :-) – Daniel Hilgarth
@Peter: You really should try my answer - it works, I promise ;-) – Daniel Hilgarth
Yes, I realized that after testing – Yaroslav

2 Answers

3
votes

In Oracle and MS SQL I would use analytical functions:

select * from
(
    select
        t.* ,
        count(Col1) over (partition by Col1) as count_col1,
        count(Col2) over (partition by Col1, Col2) as count_col2
    from yourTable t
) t
where count_col1 = 1 or count_col2 > 1;

See this fiddle (Oracle) and this fiddle (MSSQL) as proof.

2
votes
select * 
from table t1
join (select col1
      from table
      group by col1
      having avg(col2)=max(col2)) t2
     on t1.col1=t2.col1

Seeing that I didn't look at your example .. and your request is slightly different then the example, Because my query checks that for a col1 all col2 should be the same. It will not display the ones that are the same.

In this case the answer will be

select * 
from table1 t1
join (select col1,col2
      from table1
      group by col1,col2
      having count(*)>1
      union
      select col1,cast(null as varchar)
      from table1 group by col1
      having count(*)=1) t2
     on t1.col1=t2.col1 and t1.col2=isnull(t2.col2,t1.col2)

This is the updated query, and the fiddle for it http://sqlfiddle.com/#!3/e944b/2/0

Ok .. updated one more time:

select * 
from table1 t1
join (select col1,col2
     from table1
     group by col1,col2
     having count(*)>1
     union
     select col1,min(col2)
     from table1 group by col1
     having count(*)=1 or count(*)=count(distinct col2)) t2
     on t1.col1=t2.col1 and t1.col2=t2.col2

and with fiddle http://sqlfiddle.com/#!3/d5437/12/0

This should be enough for the second problem:

select t3.* 
    from (select distinct col1 from table1)t1
    cross apply (select top 1 * from table1 t2 where t1.col1=t2.col1) t3

and the fiddle: http://sqlfiddle.com/#!3/e944b/4/0