var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Max function
I have a table like this :
Name Score1 Score2 Score3 Score4
John 100 50 200 150
Smith 15 12 250 100
What I need to find is the max score for each person, so the result should be like this :
Does anyone know how to do this ?
Score 1: Score 2: Score 3
Are these seperate fields?
Or can you post the query that generates the output you have at present
Yes they are.
In excel, it's very easy to do this since they are max function to do this.
Try something like this:
select [name], (case when (case when score3 < score4 then score4 else score3 end)
<(case when score1 < score2 then score1 else score2 end) then
(case when score3 < score4 then score4 else score3 end) else
(case when score3 < score4 then score4 else score3 end)end) from table
What I did was using :
select name, score1 from table
union select name, score2 from table
Then group by the above query by name and find the max. And then, merge it back to the original table.
What I was not sure is whether to use union select OR union all select in this case ?
I tried this:
s1 is score1 and so on, it works as well.
select [name], max(Score) from
(select [name],s1 as Score from a union select [name],s2 from a union select [name],s3 from a union select [name],s4 from a) as d
group by [name]
You'll want to use union all if there are duplicate values for a person's score. However if your just finding the max it's a moot point because if the max value has a duplicate it doesn't matter.
Hope that helps,