How to get a distinct value from my data set

Question

Post reply

How to get a distinct value from my data set

skaggs.andrew

SSCrazy

Points: 2476
More actions
July 25, 2024 at 2:54 am

#4439704

Hi,
I need help with writing a script that will allow me to pull a distinct value from column A, depending on the value in column B. Here is an example of the data set:
Column A | Column B
5000 | 1
5000 | 1
5001 | 1
5001 | 2
5002 | 2
5002 | 2
If we are using this data set above, essentially I am trying to write a script that will provide me with the answer of 5001 because for this, there are both a 1 and a 2 in column B. I thought I could use the ROW_NUMBER ( ) OVER ( [ PARTITION BY ...... but I wasn't able to get this. Maybe I am using it wrong?
Any suggestions on how to get this?
Thanks in advance for any help.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Do a COUNT on both columns using GROUP BY and add a HAVING where the COUNT(*) = 1.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ken McKelvey SSCoach Points: 18842 More actions · Answer 2

SELECT ColumnA
FROM YourTable
GROUP BY ColumnA
HAVING COUNT(DISTINCT ColumnB) > 1;

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

SELECT ColumnA

FROM dbo.your_table

GROUP BY ColumnA

HAVING COUNT(DISTINCT ColumnB) = (SELECT COUNT(DISTINCT ColumnB) FROM dbo.your_table)

ORDER BY ColumnA

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".