Group by but only when 2nd column is not the same.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group by but only when 2nd column is not the same.

Author

Topic

pcpham
Starting Member

10 Posts

Posted - 2010-06-02 : 02:40:15

Hi,

I am trying to retrieve only rows 2 and 3 as Col3 occurs twice "b" but Col2 values are not the same ("a" and "d"). Where as row 7 and 8 should be ignored as the values in Col2 are the same.

[Row]: [Col1], [Col2], [Col3], [Col4]
1: 1, a, b, c
2: 1, d, b, e
3: 1, f, g, h
4: 1, i, j, k
5: 2, h, i, j
6: 2, k, l, m
7: 3, n, o, p
8: 3, n, o, p
9: 4, a, b, c

Thanks in advanced,

Paula

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-02 : 03:33:56

Why not rows 1 and 3, instead of rows 2 and 3?
What are the business rules for this?

N 56°04'39.26"
E 12°55'05.63"

pcpham
Starting Member

10 Posts

Posted - 2010-06-02 : 06:29:55

Col2 represents school name. Col3 represents schoolID. The both should be unique but data error has pointed the same schoolID to two different school names. So I need to retrieve this.

Thanks....

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-02 : 06:38:25

Thats correct but which school name you want to retrieve a or d
thats what Peso asked why 2 and 3 instead of 1 and 3.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

pcpham
Starting Member

10 Posts

Posted - 2010-06-02 : 07:19:47

Ok let me try and make the data more realistic. For every schoolid there supposed to be a unique schoolname except one of the records SchoolID3 has two different Schoolnames when they are only supposed to be 1. So I need to retrieve the records where the SchoolID has been repeated more than once with a different SchoolName assigned. In this case rows 3 and 4 has SchoolID3 belonging to two different Schools i.e. SchoolName3 and SchoolName4. It doesn't matter if the SchoolID occurs more than once as long as it has the same School name like rows 5 and 6. The query should be only interested in retrieving SchoolIDs that have been repeated but with different school names assigned.

1: SchoolID1, SchoolName1, Student1
2: SchoolID2, SchoolName2, Student2
3: SchoolID3, SchoolName3, Student4
4: SchoolID3, SchoolName4, Student5
5: SchoolID4, SchoolName5, Student6
6: SchoolID4, SchoolName5, Student7

Thanks,

Paula

GopiMuluka
Starting Member

12 Posts

Posted - 2010-06-02 : 07:37:15

Check this query

DECLARE @Table TABLE
(
SNo INT,
SchoolID VARCHAR(50),
SchoolName VARCHAR(50),
StudentID VARCHAR(50)
)
INSERT INTO @Table
SELECT 1,'SchoolID1','SchoolName1','Student1' UNION
SELECT 2,'SchoolID2','SchoolName2','Student2' UNION
SELECT 3,'SchoolID3','SchoolName3','Student4' UNION
SELECT 4,'SchoolID3','SchoolName4','Student5' UNION
SELECT 5,'SchoolID4','SchoolName5','Student6' UNION
SELECT 6,'SchoolID4','SchoolName5','Student7' UNION
SELECT 7,'SchoolID3','SchoolName333','Student10'

SELECT DISTINCT A.*
FROM @Table A
INNER JOIN @Table B
ON A.SchoolID=B.SchoolID
AND A.SchoolName<>B.SchoolName

Kristen
Test

22859 Posts

Posted - 2010-06-02 : 07:47:28

Using Gopi's data (Thanks!) if you just want to know the SchoolID where there exist one or more different SchoolName values then:


SELECT	SchoolID
FROM	@Table
GROUP BY SchoolID
HAVING COUNT(DISTINCT SchoolName) > 1

(this should perform faster then Gopi's query if the table is large) and if you need to see the individual rows (to fix them) then JOIN that back to the original table:


SELECT	*
FROM	@Table AS T
	JOIN
	(
		SELECT	SchoolID
		FROM	@Table
		GROUP BY SchoolID
		HAVING COUNT(DISTINCT SchoolName) > 1
	) AS X
		ON X.SchoolID = T.SchoolID

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-06-02 : 07:54:44

Or, with one table scan:


SELECT SchoolID, SchoolName, StudentID
FROM
(
	SELECT *
		,MIN(SchoolName) OVER (PARTITION BY SchoolID) AS MinName
		,MAX(SchoolName) OVER (PARTITION BY SchoolID) AS MaxName
	FROM @Table
) D
WHERE MinName <> MaxName

[Edit] ps You should check to see which option is most efficient with your data. [/Edit]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-02 : 08:11:19

SELECT SchoolID
FROM @Table
GROUP BY SchoolID
HAVING MIN(SchoolName) < MAX(SchoolName)

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources