Select Top 2 by group

[Chromium]

Hi, I saw some posts about this but no one works for me.. (sometimes I got my computer stuck with 2 cores at 100% percent... 30 minutes.. something goes wrong).

Query: Trasferimenti Q2
Fields: Nome giocatore (text)
Squadra Offerta (text)
Valore (integer)


That's the situation, in a new query create on "Trasferimenti_Q2" query data I need to select top 2 records of the "Nome Giocatore" field grouped by "Squadra Offerta".

Can someone help, please?

Thanks,

Lorenzo

 

[ByteMyzer]

Try:

SELECT Q1.*
FROM Trasferimenti_Q2 AS Q1
WHERE Q1.[Nome giocatore] IN
   (SELECT TOP 2 Q2.[Nome giocatore]
    FROM Trasferimenti_Q2 AS Q2
    WHERE Q2.[Squadra Offerta] = Q1.[Squadra Offerta]
    ORDER BY Q2.[Nome giocatore])
ORDER BY Q1.[Squadra Offerta], Q1.[Nome giocatore];

NOTE: make certain that field Squadra Offerta in the source table for Query Trasferimenti_Q2 is indexed. Without knowing the field structure of the source table for Query Trasferimenti_Q2, or how said query is structured, there are no further recommendations that I can make.

 

[Chromium]

It works right, but it' slow.

I have 46000 records, and about 20 groups... cuttin' em It's faster. Someone have any ideas to improve speed?

 

[lemo]

i think this is the perfect thread for you -

http://www.access-programmers.co.uk/forums/showthread.php?t=161816&highlight=slow

it's a fun read..
good luck,
l

 

[Chromium]

Thanks to all