ttist25
asked on
MS SQL Query to group rows and Identify those groups where the same value exists in a field for all rows
That title sounds a little crazy but here's what I'm trying to do.
I have groups of records that can be grouped by 3 fields. That is, if field1=field1 AND field2=field2 AND field3=field3 in two or more rows, the rows belong to the same "group".
I think I can use PARTITION BY for this (PARTITION BY field1, field2, field3)
What I need to determine is for all of the groups, how many of them have the same value in another field (e.g., FIELD4). For example, given:
FIELD1 | FIELD2 | FIELD3 | FIELD4
-------------------------- ---------- ---------
1 A 5 x
1 A 5 x
1 A 5 x
2 B 4 x
2 B 4 y
3 A 5 x
Group "1 A 5" has 'x' for all of its values in FIELD4 (this is a group I'm looking for)
Group "2 B 4" has an 'x' and a 'y' in FIELD4 (I'm not looking for this group)
Thanks in advance for any help.
I have groups of records that can be grouped by 3 fields. That is, if field1=field1 AND field2=field2 AND field3=field3 in two or more rows, the rows belong to the same "group".
I think I can use PARTITION BY for this (PARTITION BY field1, field2, field3)
What I need to determine is for all of the groups, how many of them have the same value in another field (e.g., FIELD4). For example, given:
FIELD1 | FIELD2 | FIELD3 | FIELD4
--------------------------
1 A 5 x
1 A 5 x
1 A 5 x
2 B 4 x
2 B 4 y
3 A 5 x
Group "1 A 5" has 'x' for all of its values in FIELD4 (this is a group I'm looking for)
Group "2 B 4" has an 'x' and a 'y' in FIELD4 (I'm not looking for this group)
Thanks in advance for any help.
Please try full tested solution.
CREATE TABLE AND DATA GENERATION
SOLUTION
OUTPUT
CREATE TABLE AND DATA GENERATION
CREATE TABLE Fielder
(
FIELD1 INT
,FIELD2 VARCHAR(1)
,FIELD3 INT
,FIELD4 VARCHAR(1)
)
GO
INSERT INTO Fielder VALUES
(1 ,'A',5,'x'),
(1 ,'A',5,'x'),
(1 ,'A',5,'x'),
(2 ,'B',4,'x'),
(2 ,'B',4,'y'),
(3 ,'A',5,'x')
GO
SOLUTION
SELECT F.*
FROM
(
SELECT FIELD1
FROM Fielder
WHERE FIELD4 = 'x'
GROUP BY FIELD1
HAVING COUNT(FIELD4) > 1
)k INNER JOIN Fielder F ON k.FIELD1 = f.FIELD1
OUTPUT
/*------------------------
OUTPUT
------------------------*/
FIELD1 FIELD2 FIELD3 FIELD4
----------- ------ ----------- ------
1 A 5 x
1 A 5 x
1 A 5 x
(3 row(s) affected)
ASKER
Thanks for the responses Bembi and Pawan.
Pawan, I need to group on field1, field2, and field3 - I'm not sure how to do that within your solution.
Can you help?
Pawan, I need to group on field1, field2, and field3 - I'm not sure how to do that within your solution.
Can you help?
Yes we can do the grouping. GROUP BY FIELD1,FIELD2,FIELD3
SOLUTION
OUTPUT
SOLUTION
SELECT F.*
FROM
(
SELECT FIELD1
FROM Fielder
WHERE FIELD4 = 'x'
GROUP BY FIELD1,FIELD2,FIELD3
HAVING COUNT(FIELD4) > 1
)k INNER JOIN Fielder F ON k.FIELD1 = f.FIELD1
OUTPUT
/*------------------------
OUTPUT
------------------------*/
FIELD1 FIELD2 FIELD3 FIELD4
----------- ------ ----------- ------
1 A 5 x
1 A 5 x
1 A 5 x
(3 row(s) affected)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Well my first query is already giving all the columns. The last query WILL NOT GIVE you all the required rows. IT will give you first and last row of the group.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys!
You can build a GROUPED query where you group field1, field2 and field3
For field 4 you can use the count function and your result are all records with a value of 1