Top5 for each "Loom" - again 1

[GPM4663]

Hi Again,
OK I thought I had this sussed from yesterdays post but I just can get this to work. I have the following SQL

SELECT tblLooms.Loom, [qryAnalysis].Ref, [qryAnalysis].Metres
FROM tblLooms INNER JOIN [qryAnalysis] ON tblLooms.Loom = [qryAnalysis].Loom
WHERE ((([qryAnalysis].Ref) In (select top 5 Ref from [qryAnalysis] where [qryAnalysis].Loom = tblLooms.loom order by [qryAnalysis].Metres desc)))
ORDER BY tblLooms.Loom, [qryAnalysis].SumOfMetres DESC;

I want the top 5 metres for EACH loom and this query just won't work. I've based the query on some help from yesterday but whenever I try to run it it just hangs. I can't see where I'm going wrong. I'd love some help....again.

Many thanks,

GPM

 

[Golom]

The SQL looks fine. Just to be sure that the query isn't just taking a long time, try running it for a specific "Loom"

SELECT L.Loom, Q.Ref, Q.Metres

FROM tblLooms As L INNER JOIN [qryAnalysis] As Q ON L.Loom = Q.Loom

WHERE Q.Ref In (Select TOP 5 Ref FROM [qryAnalysis] As X
                WHERE X.Loom = L.Loom ORDER BY X.Metres DESC)

      [COLOR=red]AND L.Loom = 1[/color]

ORDER BY L.Loom, Q.SumOfMetres DESC;

Set the value of "L.Loom" in the above to a valid value for one Loom in your table.

 

[GPM4663]

Thanks for the reply Golom. You were right. I tried it the way you suggested and it took about 10 mins to run. I tried it without and it was well over an hour. Even 10 mins is too long - I don't understand why it is taking so long to run. The query it is based on runs in about 2-3 seconds. Is there anything I can do or another way of getting the same result?

Thanks

GPM

 

[PHV]

Transform qryAnalysis to a maketable query creating, say, tblAnalysis and then run it.
Now, your top 5 query:
SELECT L.Loom, A.Ref, A.Metres
FROM tblLooms L INNER JOIN tblAnalysis A ON L.Loom = A.Loom
WHERE A.Ref In (SELECT TOP 5 Ref FROM tblAnalysis WHERE Loom = L.Loom ORDER BY Metres DESC)
ORDER BY L.Loom, A.Metres DESC;

BTW, what is SumOfMetres ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Golom]

You can make sure that you have indexes built on any fields that appear in an ORDER BY clause or an ON Clause. Since (I infer) qryAnalysis is another query and not a table, I would suggest that you follow PHV's advice and run a make-table and build the necessary indexes on it.

 

[GPM4663]

Thanks guys, I'll give that a go!

PHV - sumofMetres should have been Metres - I'd renamed it to make it easier to follow and avoid any confusion. I should really try harder!!!!!

Thanks again,

GPM