RDBMS - SQL Max Value of a column based upon another column's uniqueness

Question

This is really difficult question to ask. So here goes. The following sql:

select * from Survey_Answers 
where QuestionID='50a350a5-9f53-4f1a-83b8-485de45ac9a9'  
order by MaxRating

Generates the following:

QuestionID                             Answer     Value MaxRating MinRating
--------------------------------------------------------------------------
50a350a5-9f53-4f1a-83b8-485de45ac9a9   anywhere       0     1         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   anyplace       0     2         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   test           9     2         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   love           10    3         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   no one         10    4         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   feel this way  0     5         0

(6 row(s) affected)

In the MaxRating Column there are two "2"s. I would like the max value of those two columns. So for instance if there was three 1s and two 2s and five 4s, I would like the max value of the "value" column based upon distinct maxrating columns.

I hope this make sense so instead of the above the result should be:

QuestionID                             Answer     Value MaxRating MinRating
--------------------------------------------------------------------------
50a350a5-9f53-4f1a-83b8-485de45ac9a9   anywhere       0     1         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   test           9     2         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   love           10    3         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   no one         10    4         0
50a350a5-9f53-4f1a-83b8-485de45ac9a9   feel this way  0     5         0

The 9 is the max of column "Value" based upon the Maxrating column with data of "2"

This is so difficult to explain, so I hope someone understands this. Any help is gladly appreciated. Any help in re-wording this would be great. Thanks.

Answer 1

Something like this:

select *
from Survey_Answers sa1
where sa1.QuestionID = '50a350a5-9f53-4f1a-83b8-485de45ac9a9'  
and sa1.value = (select max(value) 
                 from Survey_Answers sa2
                where sa2.questionid = sa1.QuestionID
                  and sa2.maxrating = sa1.maxrating)
order by MaxRating