how to calculate top 3 results

Question

I have a table with multiple columns such as

RacerID | Round1 | Round2 | Round3 | Round4 | Total
  2         100      100      96       99      395                                            
  5         99       97       100      96      392

how can i query out the top 3 results from columns (round1, round2, round3, round4) so that it should display

racerid | Top3Rounds
   2         299                
   5         296

Many thanks :)

possible duplicate of [SQL MAX of multiple columns?](http://stackoverflow.com/questions/71022/sql-max-of-multiple-columns) — Tom, Feb 23 '15 at 08:23

score 6 · Accepted Answer · edited Feb 23 '15 at 08:28

6

may be this:

SELECT RacerID ,  round1 + round2 + round3 + round4 - LEAST(round1, round2, round3, round4) AS Top3Rounds
FROM tablename

Fiddle example here.

edited Feb 23 '15 at 08:28

Raging Bull

18,113
13
47
53

answered Feb 23 '15 at 08:26

Oto Shavadze

37,634
51
140
215

2

Took the liberty to add the fiddle. – Raging Bull Feb 23 '15 at 08:29
that's brilliant, works like a charm thanks OTARIKI and Raging Bull, all i have to do now is tweak it to make it pull in the rounds dynamically and boom job done :) – Ade Mitchell Feb 23 '15 at 09:50
sorry to be a pain guys, that code works great, but what if there were 12 rounds, and we need best of 7? many thanks – Ade Mitchell Feb 23 '15 at 10:35

how to calculate top 3 results

1 Answers1