July 25, 2024 at 2:54 am
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.
July 25, 2024 at 5:07 am
Do a COUNT on both columns using GROUP BY and add a HAVING where the COUNT(*) = 1.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 25, 2024 at 12:05 pm
SELECT ColumnA
FROM YourTable
GROUP BY ColumnA
HAVING COUNT(DISTINCT ColumnB) > 1;
July 25, 2024 at 1:41 pm
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".
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy