Looking for the advisable sql sentence

• cphspain

  Old Hand

  Points: 342

  More actions

  September 26, 2014 at 3:44 am

  #311211

  Hi,

  Let’s have the next set of data

  Incident # 162002982 has among the results 2 different id’s, these are id’s 9 &11

  Incident # 162002562 has among the results the same id’s, in this case Id’s 13

  I am only interested in get those incident # and results where among the results we find 2 or more different id’s; that means that from the sample set of data below, I am only interested in Incident # 162002982 and its results

  What type of SQL sentence can use for that purpose?

  Beforehand I appreciate your advices, thanks

  1620029829

  16200298211

  16200298211

  16200298211

  16200298211

  16200298211

  16200298211

  16200298211

  16200298211

  16200298211

  1620029829

  16200256213

  16200256213

  16200256213

  Claudio

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 26, 2014 at 4:35 am

  #1748428

  ;WITH Sampledata AS (

  SELECT Incident = 162002982, ID = 9 UNION ALL

  SELECT 162002982, 11 UNION ALL

  SELECT 162002982, 11 UNION ALL

  SELECT 162002982, 11 UNION ALL

  SELECT 162002982, 11 UNION ALL

  SELECT 162002982, 11 UNION ALL

  SELECT 162002982, 11 UNION ALL

  SELECT 162002982, 11 UNION ALL

  SELECT 162002982, 11 UNION ALL

  SELECT 162002982, 11 UNION ALL

  SELECT 162002982, 9 UNION ALL

  SELECT 162002562, 13 UNION ALL

  SELECT 162002562, 13 UNION ALL

  SELECT 162002562, 13

  )

  SELECT Incident, ID

  FROM (

  SELECT

  *,

  MIN_ID = MIN(ID) OVER(PARTITION BY Incident),

  MAX_ID = MAX(ID) OVER(PARTITION BY Incident)

  FROM Sampledata

  ) d

  WHERE MIN_ID <> MAX_ID

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• cphspain

  Old Hand

  Points: 342

  More actions

  October 15, 2014 at 2:02 am

  #1752403

  Thank you ChrisM 🙂

• serg-52

  SSCrazy Eights

  Points: 9913

  More actions

  October 15, 2014 at 2:28 am

  #1752412

  SELECT Incident, cnt = COUNT(distinct ID)

  FROM Sampledata

  GROUP BY Incident

  HAVING COUNT(distinct ID) > 1