Select top 5 records for each group

[EveA]

Hi! Please help....
I have a table [ company, sales, size, country]. I would like to have returned a table [country, average size of top 5 companies in the respective country in terms of sales].
So, first get to 5 companies with highest sales in every country. Build averages of size parameter.


In the first step I tried this but it returns empty cell:

SELECT company, sales, country
FROM MyTable t
WHERE company IN
(
SELECT TOP 3 company
FROM MyTable
WHERE company = t.company
ORDER BY sales DESC, country
)
ORDER BY company, sales DESC, country;

[orange]

There is an example in the Code repository dealing with Top N.

[EveA]

I managed to code, and it works:
SELECT Dummy.Country, Dummy.Sales, Dummy.Company, Dummy.Value
FROM Dummy
WHERE Dummy.Company IN
(SELECT TOP 2 Company
FROM Dummy AS Dupe
WHERE Dupe.Country = Dummy.Country
ORDER BY Dupe.Sales DESC, Dupe.Country DESC)
ORDER BY Dummy.Country, Dummy.Sales, Dummy.Country,Dummy.Value)

But... it works on simple tables, however with my original table with 40'000 lines it takes forever. Any idea how to speed up?

[orange]

Suggest you post a copy of your data base with just enough records (100 or so) to show us typical data.
Also recommend you compact and repair the database and post it as a zip file.

The Title of your post is Top 5, but in your sample SQL you don't deal with 5???

I set up 100 sample records and used the following based on your posted SQL

SELECT DUMMY.tCountry
    ,DUMMY.nSales
    ,DUMMY.tCompany
FROM CompInfo AS DUMMY
WHERE DUMMY.tCompany IN (
        SELECT TOP 5 tCompany
        FROM Compinfo AS Dupe
        WHERE Dupe.tCountry = DUMMY.tCountry
        ORDER BY Dupe.nSales DESC
            ,Dupe.tCountry DESC
        )
ORDER BY DUMMY.tCountry
    ,DUMMY.nSales DESC
    ,DUMMY.tCompany

Sample output:

tCountry
nSales
tCompany

Bahamas
73383
Massa Quisque Foundation

Bahamas
47538
Nulla Associates

Bahamas
44320
Commodo Tincidunt Nibh Corporation

Bahamas
42419
Nunc Limited

Bahamas
31655
Vel Sapien LLC

Canada
89260
Convallis Inc.

Canada
85855
Sit Amet PC

Canada
81097
Eget Metus Eu Limited

Canada
77678
Eu Ultrices Sit LLC

Canada
73302
Magna Praesent Company

Denmark
70122
Vestibulum Massa Rutrum PC

Denmark
43782
Sapien Cursus Ltd

Denmark
43079
Vehicula Pellentesque Foundation

Denmark
20037
Ultrices Iaculis Odio Foundation

Denmark
13667
Malesuada Malesuada LLC