I’m resurrecting an app I wrote two years ago that we used to collect (and judge) online award applications. People could submit nominations, and then judges could score them.
The version from two years ago was really straightforward. There were 8 criteria with a score of 1-5, so everything was nice and round, and I could arrive at 100% very easily.
This year there’s a twist. There are 9 criteria, and they only want to count the 7 highest scores out of those. I have to admit, I have NO idea how to implement this.
Here’s my SQL from the old version, modified for the 9 criteria:
SELECT nominees.nom_id
, nominees.nom_title
, nominees.nom_summary
, nominees.nom_firstname & ' ' & nom_lastname AS nominee
, judges.user_id AS judge_id
, judges.user_name AS judge
, users.user_name AS nominator
, users.user_org
, a.avg_score
, ROUND(scores.cpi_score * 2.222
+ scores.inno_score * 2.222
+ scores.pc_score * 2.222
+ scores.int_score * 2.222
+ scores.equ_score * 2.222
+ scores.ls_score * 2.222
+ scores.drer_score * 2.222
+ scores.iis_score * 2.222
+ scores.css_score * 2.222) AS total_score
FROM ( ( ( ( (
nominees
INNER JOIN users
ON users.user_id = nominees.submit_id
)
LEFT OUTER JOIN judgenom
ON judgenom.nomination_id = nominees.nom_id
)
LEFT OUTER JOIN users AS judges
ON judges.user_id = judgenom.judge_id
)
LEFT OUTER JOIN scores
ON scores.nomination_id = nominees.nom_id
)
LEFT OUTER JOIN (
SELECT scores.nomination_id
, AVG( scores.cpi_score * 2.222
+ scores.inno_score * 2.222
+ scores.pc_score * 2.222
+ scores.int_score * 2.222
+ scores.equ_score * 2.222
+ scores.ls_score * 2.222
+ scores.drer_score * 2.222
+ scores.iis_score * 2.222
+ scores.css_score * 2.222) AS avg_score
FROM scores
GROUP BY scores.nomination_id ) AS a
ON a.nomination_id = nominees.nom_id
)
WHERE scores.judge_id = judgenom.judge_id
OR scores.judge_id IS NULL
ORDER BY avg_score DESC, nominees.nom_id, judgenom.judge_id
Any hints on how to make it consider only the top seven submitted scores?