Top 10% score (what about ties)

[jmccullough]

I need to report the top 10% scores. Here is my query when selecting "All" values. I have 20 records so the 10% value should show me two (2) records but since I have a tie in the second value I should get 4 records.

Total2Team NameSchool District Name22Ancient HistoriansFranklin Area21Ancient HistoriansFranklin Area21Ancient HistoriansFranklin Area21Ancient HistoriansFranklin Area20"It's Morning Again in America"Grove City Area20"It's Morning Again in America"Grove City Area20"It's Morning Again in America"Grove City Area20"It's Morning Again in America"Grove City Area20"It's Morning Again in America"Grove City Area20"Walk This Way"Grove City Area20"Walk This Way"Grove City Area19Back in BlackGrove City Area19Back in BlackGrove City Area19Back in BlackGrove City Area19Ancient HistoriansFranklin Area19"Walk This Way"Grove City Area19"Walk This Way"Grove City Area19Back in BlackGrove City Area18"Walk This Way"Grove City Area17Back in BlackGrove City Area

But this is what I get in Access 2007, why am I getting the extra records?

Total2Team NameSchool District Name22Ancient HistoriansFranklin Area21Ancient HistoriansFranklin Area21Ancient HistoriansFranklin Area21Ancient HistoriansFranklin Area20"Walk This Way"Grove City Area20"Walk This Way"Grove City Area20"It's Morning Again in America"Grove City Area20"It's Morning Again in America"Grove City Area20"It's Morning Again in America"Grove City Area20"It's Morning Again in America"Grove City Area20"It's Morning Again in America"Grove City Area

 

[Banana]

I'm afraid I can't read your sample data, and it generally is more helpful to post your SQL .

Because we're after all top 10%, even if this is not actually 10% of total answers (e.g. 12% of all answers representing the top 10% of correct answers for example), TOP predicate is not appropriate here. We would probably want to use a WHERE criteria with a subquery to get the number that is the threshold of 10%, then return all records greater than that number.

Example:

SELECT student, score FROM tblresult WHERE score >= (SELECT TOP 1 score FROM tblresult WHERE score >= (score * .9) ORDER BY score DESC);

 

[jmccullough]

Sorry for the garbled mess. I will try your solution and let you know.

Thanks very much, I appreciate your quick response.