Top 3 results in each group required

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to list the top 3 finishers in a race grouped by the club they
belong to. Can anyone suggest how, please?
 
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
 
Back
Top