Maybe a NOOB question, but... Help!

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

SQL Server 2005 Forums

Transact-SQL (2005)

Maybe a NOOB question, but... Help!

Author

Topic

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-05-13 : 14:50:53

I need a query to find records that all have the same values in column A but different values in column B. So if the table has

A..........B
12.........1
3..........2
4..........9
3..........2
3..........17
12.........2
4..........9

It should ignore the rows with 4 in A and 9 in B but kick the rest.

Kristen
Test

22859 Posts

Posted - 2010-05-13 : 15:33:13

[code]
SELECT DISTINCT Col_A, Col_B
FROM MyTable AS T1
JOIN
(
SELECT Col_A
FROM MyTable
GROUP BY Col_A, Col_B
HAVING COUNT(*) = 1
) AS T2
ON T2.Col_A = T1.Col_A
[/code]
Untested, and I *hate* the idea of having a DISTINCT in there

Kristen
Test

22859 Posts

Posted - 2010-05-13 : 15:34:28

[code]
SELECT Col_A, Col_B
FROM MyTable AS T1
WHERE T1.Col_A IN
(
SELECT Col_A
FROM MyTable
GROUP BY Col_A, Col_B
HAVING COUNT(*) = 1
)
[/code]
also untested! but avoids the DISTINCT

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-13 : 16:36:01

quote:
Originally posted by Kristen

Untested, and I *hate* the idea of having a DISTINCT in there

You'd scream if you saw the queries I'm currently writing. DISTINCT needed just about everywhere....

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-05-13 : 17:02:07

They need to pay you more to optimise more Gail

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-14 : 04:39:25

They're as optimal as I can manage. I don't intentionally write badly-performing queries.

The table layout's odd and rows are added whenever a change is made, so getting dup results is normal.

--
Gail Shaw
SQL Server MVP

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-05-14 : 08:41:49

Thanks guys. Both of these are close, but they both return the unique rows as well. I need to just get the rows where there are multiple instances of the value in Col_A with different values in Col_B. Basically Col_A is an Employee ID number and Col_B is a value that has to be the same for every occurrence of that ID number.

So if 123456 only shows up in Col_A once, it can be ignored. If it shows up n times but Col_B has the same value all n times, thqat can also be ignored. But if Col_B has different values for different occurrences of 123456, those are the records I need.

Kristen
Test

22859 Posts

Posted - 2010-05-14 : 09:42:37

In a rush, but this perhaps? (which solves the DISTINCT problem too!)


SELECT Col_A, Col_B
FROM MyTable AS T1
WHERE T1.Col_A IN
(
    SELECT Col_A
    FROM
    (
        SELECT Col_A
        FROM MyTable
        GROUP BY Col_A, Col_B
        HAVING COUNT(*) = 1
    ) AS X
    GROUP BY Col_A
    HAVING COUNT(*) >= 2
)

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-05-14 : 10:07:25

Kristen - that seems to have done it - thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources