CODEWITHSUNDEEP
sqlt-sql
Sebastian
Sebastian

Reputation: 972

find different data with sql

I have an Table that looks like

╔══════╦══════╦═══════╦═══════╦═══════╦═══════════════════╗
║ Key1 ║ Key2 ║ Data1 ║ Data2 ║ Data3 ║ DataNotImportant4 ║
╠══════╬══════╬═══════╬═══════╬═══════╬═══════════════════╣
║ Foo  ║ Bar  ║     1 ║     2 ║     a ║                 4 ║
║ Foo  ║ Bar  ║     1 ║     2 ║     a ║                 5 ║
║ Foo  ║ Bar  ║     1 ║     2 ║     b ║                 4 ║
║ Foo  ║ Test ║     1 ║     2 ║     f ║                 4 ║
╚══════╩══════╩═══════╩═══════╩═══════╩═══════════════════╝

Now I would select the combinations of Key1 and Key2 where Data1 Data2 Data3 differs, so here I would get Foo Bar as result because Data3 is not the same in alle rows. How can I archieve this without an cursor? Database is SQL Server 2008

Currently I use a cursor. I select all key combinations with a group by and iterate through the list and make a group by select on my fields with the keys I get from my cursor. The problem is when there are too many rows its getting real slow. So I search for a set based solution.

Upvotes: 1

Views: 101

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460098

You can use Group By on multiple columns.

SELECT Key1=Min(f.key1), 
       Key2=Min(f.key2), 
       f.data1, 
       f.data2, 
       f.data3, 
       DataNotImportant4=Min(datanotimportant4) 
FROM   foo f 
GROUP  BY f.data1, 
          f.data2, 
          f.data3 
HAVING Count(*) > 1

Demo

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239654

Use a group by expression and rely on the fact that, if there are different values in one column, that must imply that the minimal value and the maximal value in that column are different:

declare @t table (key1 varchar(5),key2 varchar(5),Data1 int,Data2 int,Data3 int)
insert into @t (key1,key2,Data1,Data2,Data3) values
('Foo','Bar',1,2,3),
('Foo','Bar',1,2,3),
('Foo','Bar',1,2,4),
('Foo','Test',1,2,3)

select key1,key2 from @t
group by key1,key2
having
    MIN(data1)!=MAX(data1) or
    MIN(data2)!=MAX(data2) or
    MIN(data3)!=MAX(data3)

Result:

key1  key2
----- -----
Foo   Bar

Upvotes: 4

Related Questions