Query to retrieve average of last 3 records (1 Viewer)

[cpampas]

Hello,
I ve been struggling with this for a while now. i wonder if someone can help.
I have a table with the following data :

id jogoPla pw ANum 252784 Almere City Fc - Telstar 0,437538461 0 250939 Almere City Fc - Telstar 0,427038079 1 248979 Almere City Fc - Telstar 0,435046625 2 246034 Almere City Fc - Telstar 0,462670973 3 244191 Almere City Fc - Telstar 0,462670973 4 242250 Almere City Fc - Telstar 0,447449777 5 239485 Almere City Fc - Telstar 0,447449777 6 334186 Aberdeen - Kilmarnock 0,528046266 0 331538 Aberdeen - Kilmarnock 0,533965215 1 328899 Aberdeen - Kilmarnock 0,516046295 2 326269 Aberdeen - Kilmarnock 0,516046295 3 323661 Aberdeen - Kilmarnock 0,516046295 4 321786 Aberdeen - Kilmarnock 0,516046295 5 318991 Aberdeen - Kilmarnock 0,524472591 6 316110 Aberdeen - Kilmarnock 0,524472591 7 313217 Aberdeen - Kilmarnock 0,524472591 8

I would like to get the average of the fields (pw), with the 3 highest (ANum), in this case :

Almere City Fc - Telstar	0,452523509​
Aberdeen - Kilmarnock	0,524472591​

I've tried the following query using dcount to count the number of records in each field (jogoPla), and the result is OK, except for the fact that it takes 1 minute to execute this query alone

SELECT tblLastW_Pinn.jogoPla, Avg(tblLastW_Pinn.pw) AS MédiaDepw
FROM tblLastW_Pinn
WHERE (((tblLastW_Pinn.ANum)>DCount("[jogopla]","tblLastW_Pinn","[jogopla]=""" & [jogoPla] & """")-4))
GROUP BY tblLastW_Pinn.jogoPla;

Perhaps there is another way to make it faster
Thanks

 

[CJ_London]

main thing that affects speed is indexing, your jogopla and anum fields should both be indexed

to get the last 3 records you need to use a subquery within your query, so try something like this

[CODE
SELECT jogoPla, Avg(pw) AS AvgOfpw
FROM myTable
WHERE ID In (SELECT TOP 3 ID FROM mytable T WHERE jogopla=myTable.jogopla ORDER BY Anum Desc)
GROUP BY jogoPla;
[/CODE]

edit: corrected code for missing group by clause and field name typo

 

[plog]

You have assumed that ANum will always be consecutive for an jogoPla--is that accurate? Records won't be deleted? Records will always be added with the next Anum? These things you are certain of, or just assumed? The first thing I'd do is test my data to make sure those assumptions are correct:

SELECT jogoPla
FROM tblLastW_Pinn 
GROUP BY jogoPla
HAVING (COUNT(jogoPla) - MAX(Anum) <> 1)

If that query returns any records your assumptions are incorrect. If it returns none, you can proceed with your method. Then I would change your method to not use a DCOUNT, but a subquery:

SELECT jogoPla, MAX(Anum) - 4 AS AnumBase
FROM tblLastW_Pinn 
GROUP BY jogoPla

Save that query, then change the query you posted above to use it. Add it in an INNER JOIN and then join it by jogoPla fields (equally) and ANum to AnumBase (ANum > AnumBAse). That should make your query more efficient.

 

[cpampas]

Hi again,
I ended up following plog's advice , and since Anum is consecutive, and no deletions are allowded, I created the two queries :

SELECT tblLastW_Pinn.jogoPla, Max([Anum])-4 AS AnumBase
FROM tblLastW_Pinn
GROUP BY tblLastW_Pinn.jogoPla;

SELECT tblLastW_Pinn.jogoPla, Avg(tblLastW_Pinn.pw) AS MédiaDepw, qryWFirst_pinn1.AnumBase
FROM qryWFirst_pinn1 INNER JOIN tblLastW_Pinn ON qryWFirst_pinn1.jogoPla = tblLastW_Pinn.jogoPla
WHERE (((tblLastW_Pinn.ANum)>[AnumBAse]))
GROUP BY tblLastW_Pinn.jogoPla, qryWFirst_pinn1.AnumBase;

I also indexed jogopla and anum fields as cj_London said
And It all works like a charm

Tanks for your kind help