Subquery works..2 questions to learn more

[Bob Barnes]

Thanks to Allen Browne, and general reading of the other Posts,
I have a Top 5 Aggregate Query working, but I'd like to learn more.

1....
If I use the name of the Aggregate Query in the Subquery, it runs, but
much slower than making that Aggregate Query a Table (which I've
read before, while it works, is discouraged). Is this observation I've
noted the way it is when using an Aggregate Query as a Subquery?

2..
If the Top 5 results are 7, 6, 5, 4, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3,
3, 3, 3
then all the 3s are displayed...they are a tie for 5th place...any way to
display only one 3...although the other "3" corresponding data has Mgmt Info.

TIA - Bob

 

[Allen Browne]

Congrats, Bob: you will find subqueries very useful (as wel as somewhat
frustrating), so it will prove a good learning experience.

Re your specific questions:

1) In general, JET does tend to execute subqueries slower than most other
approaches (such as JOINs, stacked queries, or temp tables.) So while
subqueries have a wide range of uses, they are not the answer to everything.

If there is an easy, efficient solution that does not require a temp table,
you will want to go that way. However, there are many cases where a temp
table makes good sense. In general, you use a temp table if performance has
become a real barrier, or if you need to reuse the results from the temp
table.

More on solving performance issues:
http://allenbrowne.com/subquery-02.html#Performance

2) To solve the fact that JET returns more than the TOP N results when there
are ties, give it some way to decide between equals. The simplest way to do
this is to add the primary key field to the ORDER BY clause. More info:
http://allenbrowne.com/subquery-01.html#TopN

 

[Bob Barnes]

Allen - thank you.

I'm using a Make Table as it runs so much faster.

The "Top 5" results give very beneficial info, and, after discussion w/ the
Client, the "tie for 5th Place" DOES need to show any other tie-values.

As always, you, and the other fine Newsgroup contributors, merit a High-Five.

Thanks again - Bob

 

[Allen Browne]

Okay, sounds like you have things sorted out.

I'm surprised at your comment that the Make Table runs faster. I never use a
Make Table query in a production database, any only very rarely when
developing. I find it much more reliable to set up the target table exactly
right, and I don't expect this to execute any slower:
db.Execute "DELETE FROM MyTempTable;", dbFailOnError
db.Execute "INSERT INTO ...