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.

• Jeff Moden

  SSC Guru

  Points: 1004340

  More actions

  July 25, 2024 at 5:07 am

  #4439720

  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

  July 25, 2024 at 12:05 pm

  #4439775

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

• ScottPletcher

  SSC Guru

  Points: 101098

  More actions

  July 25, 2024 at 1:41 pm

  #4439813

  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".