[AccessD] Getting the Top 3 Records of every n

Yes, I ran into this as well and it is the select query in the IN() clause
that is the key.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Francisco Tapia
Sent: Friday, April 20, 2007 11:36 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Getting the Top 3 Records of every n

Gustav,
  It was actually pretty simple, I received this from another colleague

SELECT CustomerID, OrderDate
FROM Orders
WHERE OrderID In (SELECT TOP 3 OrderID FROM Orders O WHERE O.CustomerID =
Orders.CustomerID ORDER BY OrderDate Desc) ORDER BY CustomerID, OrderDate
DESC;

which actually does what I needed.

--
Francisco

On 4/20/07, Gustav Brock <Gustav at cactus.dk> wrote:
>
> Hi Francisco
>
> Did you ever get a solution to this?
> If not, look the archive:
>
> http://databaseadvisors.com/mailman/htdig/accessd/2003-May/006636.html
>
> /gustav
>
> >>> fhtapia at gmail.com 17-04-2007 21:18 >>>
> I know how to do this in Sql Server, but have been thinking it over on 
> how to do this via the QBE grid in Access...
>
>
> The situation is there is a list like so
>
> Bob  100
> Bob  090
> Bob  050
> Bob  010
> Bill   250
> Bill   100
> Bill   070
> Bill   050
> Jim   etc...
>
>
> What is required, is that they want to be able to obtain the top 3 
> records for each customer ie, Top 3 records sorted in descending order by
the qty.
>
> Thanks guys
>
> --
> -Francisco
> http://sqlthis.blogspot.com | Tsql and More...
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



--
-Francisco
http://sqlthis.blogspot.com | Tsql and More...
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com