MS Access SQL, SELECT last five highest values in a column

Question

For example I have table named games, I want top five games with highest likes.

score 6 · Answer 1 · edited May 23 '17 at 12:29

6

Something like this:

select top 5 *
from games
order by likes desc, id desc

I have added a "tie-breaker" to the order by clause to prevent access returning more than 5 records, i.e. the primary key.

Thanks to @Fionnuala for pointing out this issue with Access. For more info, see here

edited May 23 '17 at 12:29

Community

answered Sep 11 '14 at 21:30

Donal

Top 5 will not necessarily return 5 records. All matched records will be returned. There are ways around if only 5 records are required. – Fionnuala Sep 11 '14 at 23:21
@Fionnuala please explain. – Donal Sep 11 '14 at 23:26
1

If ten records in the sort column `likes` have top 5 values, 10 records will be returned. – Fionnuala Sep 11 '14 at 23:30
@Fionnuala I did not know that. http://stackoverflow.com/questions/887787/access-sql-using-top-5-returning-more-than-5-results – Donal Sep 11 '14 at 23:35
Ansi 92 may not be the best idea, depending where you are in development. You can sort on two fields / columns to get a return of 5 rows, eg `order by likes, uniqueid desc` – Fionnuala Sep 11 '14 at 23:38
@Fionnuala yes, the "tie-breaker" - makes sense. Thanks for the info. – Donal Sep 11 '14 at 23:41

1 Answers1