August 18, 2010 at 6:05 am
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
August 18, 2010 at 6:17 am
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
August 18, 2010 at 6:18 am
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
August 18, 2010 at 6:36 am
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
August 18, 2010 at 6:41 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply