Hi guys,
im trying to get a querie that will give me the top 3 scores based on section and category,
I have the below querie that gives me the results, but when i specify a different category and sectionm the ranking number is based on the whole record set.
Code:
SELECT qfinal1.T_Category.DESC AS CategoryDesc, qfinal1.T_SECTION.DESC AS SectionDesc, qfinal1.EXHIBIT_ID, qfinal1.[Total Score], (select count (*) from q_judges_final_Scores where [total score] > [qfinal1].[total score])+1 AS ranking, qfinal1.SECTION, qfinal1.CATEGORY, qfinal1.LABEL_MEMO
FROM Q_Judges_Final_Scores AS qfinal1
GROUP BY qfinal1.T_Category.DESC, qfinal1.T_SECTION.DESC, qfinal1.EXHIBIT_ID, qfinal1.[Total Score], qfinal1.SECTION, qfinal1.CATEGORY, qfinal1.LABEL_MEMO
HAVING (((qfinal1.[Total Score])<>0))
ORDER BY qfinal1.[Total Score] DESC;
I want the rank number to reset for the top 3 of each section/category..
im close with this , i know i can add TOP 3 after the select, but i dont know how to get the rank number to be based on the same filter...
Anyw help would be great!
Thanks