SQL – Simple query with MS Access – Best In Class per Class

In this article with MS Access I will show how to create a good MS Access query, which results in best in class results. Let’s imagine, that we have the following data:

Data_One

In this database, we have four columns – Country, City, TeamID and Score. The question is: “How should we select the teams with the highest scores per country?”. OK, with 14 teams we can do it manually, but I am aware that if you are reading this post, this is not what you have expected. So, we should try some basic SQL.

After spending much more time in the Internet, than I am willing to admit, I was able to construct the following SQL code:

Well, what it does is kind of acceptable – it shows the best team per country and sorts them in a descending order. In order to make it a little more complicated, we would request information only about the cities with more than one team. We just do the following in MS Access:

MoreOrEqual2

With the so generated query the result is beautiful:

BestALl

We see the requested info for all the cities with more than one team. Thus Enschede and Warsaw are not present. The SQL for this query is the following:

We see, that the effect for <=2 is translated into SQL with the key word “HAVING”.

At last I have decided to show only the best Bulgarian and Dutch teams. For this I have generated a second query. The SQL code there is as follows:

You see, that the effect for filtering by countries is achieved again with the key words “HAVING” and “OR”. The countries are mentioned separately.

Thanks for reading the whole article. If you want to play by yourself with this two SQLs and you are not willing to spend 2 minutes on data entry for the creation of the database, you may use my file. Feel free to make  any edition of the code 🙂

Here is the file!

Enjoy it!

About

VBA Developer