Correlated MAX query in where clause, when there are more than one max value

• steven.fleck

  SSC Enthusiast

  Points: 112

  More actions

  August 18, 2010 at 6:05 am

  #225968

  I have a sense that this should be straightforward, but am stuck so would very much appreciate some help.

  I have a table with several "constructs". For each construct, there are four importance levels, and each importance level has an associated probability value. For each construct, I need to select the importance level with the largest probability level. I do this with a correlated subquery in the where clause to get the max probability for each importance level. This works well - except when two importance levels for a construct have the exact same probability. Then I have a problem, as I get both (all) importance levels with the max probability.

  When I have tied max probabilities, I need to get one of the tied importance levels. Ideally, it should be the one with the largest importance level integer value, but it can also be randomly selected.

  Here's some code to replicate my table:

  IF OBJECT_ID('TempDB..#ProbabilitiesTable','U') IS NOT NULL

  DROP TABLE #ProbabilitiesTable

  CREATE TABLE #ProbabilitiesTable

  (

  ConstructNo int,

  ImportanceBands int,

  Probability decimal(7,6)

  )

  INSERT INTO #ProbabilitiesTable

  SELECT '9','1','0.307692' UNION ALL

  SELECT '9','2','0.384615' UNION ALL

  SELECT '9','3','0.307692' UNION ALL

  SELECT '9','4','0.000000' UNION ALL

  SELECT '10','1','0.846154' UNION ALL

  SELECT '10','2','0.076923' UNION ALL

  SELECT '10','3','0.076923' UNION ALL

  SELECT '10','4','0.000000' UNION ALL

  SELECT '11','1','0.923077' UNION ALL

  SELECT '11','2','0.076923' UNION ALL

  SELECT '11','3','0.000000' UNION ALL

  SELECT '11','4','0.000000' UNION ALL

  SELECT '12','1','0.846154' UNION ALL

  SELECT '12','2','0.153846' UNION ALL

  SELECT '12','3','0.000000' UNION ALL

  SELECT '12','4','0.000000' UNION ALL

  SELECT '13','1','0.461538' UNION ALL

  SELECT '13','2','0.384615' UNION ALL

  SELECT '13','3','0.153846' UNION ALL

  SELECT '13','4','0.000000' UNION ALL

  SELECT '14','1','0.692308' UNION ALL

  SELECT '14','2','0.307692' UNION ALL

  SELECT '14','3','0.000000' UNION ALL

  SELECT '14','4','0.000000' UNION ALL

  SELECT '15','1','0.615385' UNION ALL

  SELECT '15','2','0.307692' UNION ALL

  SELECT '15','3','0.076923' UNION ALL

  SELECT '15','4','0.000000' UNION ALL

  SELECT '16','1','0.692308' UNION ALL

  SELECT '16','2','0.230769' UNION ALL

  SELECT '16','3','0.076923' UNION ALL

  SELECT '16','4','0.000000' UNION ALL

  SELECT '17','1','0.076923' UNION ALL

  SELECT '17','2','0.153846' UNION ALL

  SELECT '17','3','0.076923' UNION ALL

  SELECT '17','4','0.692308' UNION ALL

  SELECT '18','1','0.000000' UNION ALL

  SELECT '18','2','0.000000' UNION ALL

  SELECT '18','3','0.000000' UNION ALL

  SELECT '18','4','1.000000' UNION ALL

  SELECT '19','1','0.538462' UNION ALL

  SELECT '19','2','0.230769' UNION ALL

  SELECT '19','3','0.153846' UNION ALL

  SELECT '19','4','0.076923' UNION ALL

  SELECT '20','1','0.000000' UNION ALL

  SELECT '20','2','0.000000' UNION ALL

  SELECT '20','3','0.307692' UNION ALL

  SELECT '20','4','0.692308' UNION ALL

  SELECT '21','1','0.076923' UNION ALL

  SELECT '21','2','0.307692' UNION ALL

  SELECT '21','3','0.538462' UNION ALL

  SELECT '21','4','0.076923' UNION ALL

  SELECT '22','1','0.076923' UNION ALL

  SELECT '22','2','0.307692' UNION ALL

  SELECT '22','3','0.461538' UNION ALL

  SELECT '22','4','0.153846' UNION ALL

  SELECT '23','1','0.076923' UNION ALL

  SELECT '23','2','0.230769' UNION ALL

  SELECT '23','3','0.000000' UNION ALL

  SELECT '23','4','0.692308' UNION ALL

  SELECT '24','1','0.846154' UNION ALL

  SELECT '24','2','0.153846' UNION ALL

  SELECT '24','3','0.000000' UNION ALL

  SELECT '24','4','0.000000' UNION ALL

  SELECT '25','1','0.384615' UNION ALL

  SELECT '25','2','0.384615' UNION ALL

  SELECT '25','3','0.230769' UNION ALL

  SELECT '25','4','0.000000' UNION ALL

  SELECT '26','1','1.000000' UNION ALL

  SELECT '26','2','0.000000' UNION ALL

  SELECT '26','3','0.000000' UNION ALL

  SELECT '26','4','0.000000' UNION ALL

  SELECT '27','1','0.461538' UNION ALL

  SELECT '27','2','0.153846' UNION ALL

  SELECT '27','3','0.307692' UNION ALL

  SELECT '27','4','0.076923' UNION ALL

  SELECT '28','1','0.538462' UNION ALL

  SELECT '28','2','0.153846' UNION ALL

  SELECT '28','3','0.230769' UNION ALL

  SELECT '28','4','0.076923'

  This is my current query that returns multiple importance levels when there are tied probabilities (construct 25 is tied on importance level 1 and 2):

  FROM #ProbabilitiesTable Probabilities

  WHERE Probabilities.Probability =

  (SELECT MAX(p.Probability)

  FROM #ProbabilitiesTable p

  WHERE p.ConstructNo = Probabilities.ConstructNo)

  ORDER BY Probabilities.ConstructNo

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 18, 2010 at 6:17 am

  #1208419

  steve i think all you need to do is instead of getting the max(probability), you just createa a subquery with a group by construc, and join to that;

  based on your table and data you posted(thank you SO much!!)

  i think this is getting you to what you were afdter:

  SELECT *

  FROM #ProbabilitiesTable p

  LEFT OUTER JOIN (

  SELECT

  z.ConstructNo,

  MAX(z.Probability) As MProbability

  FROM #ProbabilitiesTable z

  GROUP BY z.ConstructNo

  ) X

  ON p.ConstructNo = X.ConstructNo

  ORDER BY p.ConstructNo

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• matzer

  SSCrazy Eights

  Points: 9727

  More actions

  August 18, 2010 at 6:18 am

  #1208421

  Is this what you want?

  ;with OrderedProbability as

  (

  select

  ConstructNo, ImportanceBands, Probability,

  row_number() over (partition by ConstructNo order by Probability desc, ImportanceBands desc) rn

  from

  #ProbabilitiesTable

  )

  select

  ConstructNo, ImportanceBands, Probability

  from

  OrderedProbability

  where

  rn = 1

• steven.fleck

  SSC Enthusiast

  Points: 112

  More actions

  August 18, 2010 at 6:36 am

  #1208428

  Many thanks Lowell.Unfortunately, the resultset I get is not quite what I am after. I get all the importance levels for each construct, not just the importance level with the highest probability for each construct:

  This is what I get from your query:

  ConstructNo ImportanceBands Probability ConstructNo MProbability

  9 1 0.307692 9 0.384615

  9 2 0.384615 9 0.384615

  9 3 0.307692 9 0.384615

  9 4 0.000000 9 0.384615

  10 1 0.846154 10 0.846154

  10 2 0.076923 10 0.846154

  10 3 0.076923 10 0.846154

  10 4 0.000000 10 0.846154

  This is what I am after:

  ConstructNo ImportanceBands Probability

  9 2 0.384615

  10 1 0.846154

• steven.fleck

  SSC Enthusiast

  Points: 112

  More actions

  August 18, 2010 at 6:41 am

  #1208433

  Peter - this gets me the result that I want - many thanks! However, this query is in fact part of a much larger and more complex query, so I will have to do a bit of work to see how best to incorporate the CTE into the bigger picture. Lowell's approach of joining in a subquery might be easier to integrate into the larger query, so hopefully I can get that to work as well.