Top 3 results in each group required

A

ancient_hilly

I am trying to list the top 3 finishers in a race grouped by the club they
belong to. Can anyone suggest how, please?
 
P

PZStraube

Roger,
Your Acccess Library is AWESOME. You should be getting my donation
shortly via PayPal. Question related to this thread and your sample
query, please, if your time permits:

The TopQuery.mdb Query, "Top3InvoicesForEachAccount(solution)" refers
to "I2", which I is presume is a table or query. However, I don't see
any object named I2 anywhere in the database. In fact, I don't see the
I1 that is used in this query other than its use in the query. I'm sure
there is a simple explanation but I am completely at a loss about this.
Once I understand where the I2 comes into play, I think I can convert
that query into what I need for a project I am currently working on.
Thanks very much!



****************************
 
R

Roger Carlson

I1 and I2 are aliases for the Invoices table. If you look at the SQL view,
you will see the query as:

SELECT I1.Account, Max(I1.OrderNum) AS MaxOfOrderNum, I1.[Total Price]
FROM Invoices AS I1
GROUP BY I1.Account, I1.[Total Price]
HAVING (((I1.[Total Price]) In (select top 3 [Total Price] from Invoices I2
where I1.account = I2.account order by I2.[total price] desc, I2.OrderNum
desc)))
ORDER BY I1.Account, I1.[Total Price] DESC;

This line:
FROM Invoices AS I1
does the aliasing as does:
from Invoices I2
in the subquery

You could use A and B, or Invoices1 and Invoices2, or whatever you want.

The reason is for the aliases is that I need to use the same table twice,
but this will confuse SQL. So the alias allows you to use them as if they
were two separate tables, even though they are one.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
P

PZStraube

Roger,
Thanks again for your time in helping me.
I finally dug further in and now see the I1 and I2. Very interesting.
Little by little I am catching on.
Fortunately or unfortunately, I have so many projects due over the next
few weeks that I doubt I'll be able to focus enough on this project to
grasp it adequately. I think I may just try to hire someone to write
the queries I need then try to learn from what they have done.
I will keep reviewing your suggestions, especially the correlated
subquery concept.
Thanks again for helping out a rookie.


Roger said:
I1 and I2 are aliases for the Invoices table. If you look at the SQL
view,
you will see the query as:

SELECT I1.Account, Max(I1.OrderNum) AS MaxOfOrderNum, I1.[Total Price]
FROM Invoices AS I1
GROUP BY I1.Account, I1.[Total Price]
HAVING (((I1.[Total Price]) In (select top 3 [Total Price] from
Invoices I2
where I1.account = I2.account order by I2.[total price] desc,
I2.OrderNum
desc)))
ORDER BY I1.Account, I1.[Total Price] DESC;

This line:
FROM Invoices AS I1
does the aliasing as does:
from Invoices I2
in the subquery

You could use A and B, or Invoices1 and Invoices2, or whatever you
want.

The reason is for the aliases is that I need to use the same table
twice,
but this will confuse SQL. So the alias allows you to use them as if
they
were two separate tables, even though they are one.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


"PZStraube" [email protected] wrote in
message

Roger,
Your Acccess Library is AWESOME. You should be getting my donation
shortly via PayPal. Question related to this thread and your sample
query, please, if your time permits:

The TopQuery.mdb Query, "Top3InvoicesForEachAccount(solution)" refers
to "I2", which I is presume is a table or query. However, I don't
see
any object named I2 anywhere in the database. In fact, I don't see
the
I1 that is used in this query other than its use in the query. I'm
sure
there is a simple explanation but I am completely at a loss about
this.
Once I understand where the I2 comes into play, I think I can convert
that query into what I need for a project I am currently working on.
Thanks very much!



****************************

Roger Carlson Wrote:-
On my website (www.rogersaccesslibrary.com) there is a small sample
database
called "TopQuery.mdb" which illustrates how to do this.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

"ancient_hilly" [email protected] wrote in
message
I am trying to list the top 3 finishers in a race grouped by the
club
they
belong to. Can anyone suggest how, please?--
Click to expand...
 
Top