[AccessD] TOP 30 for multiple theaters.

Hello Erwin and Scott,

Nice one Scott, 

Erwin, I am presuming at you do not have an Oracle backend, but if you
do, here is an alternative to Scott's elegant sql

-- This is for Oracle 8i
SELECT * FROM (
  SELECT tabtype,tname,ROW_NUMBER()
  OVER (
    PARTITION BY tabtype ORDER BY tname DESC
  ) Top3 FROM tab
)
WHERE Top3 <= 3


This does the same thing really, you can see that it looks for a
result set OVER a partition of data.  A nice way to handle it.

I do not think that Jet supports anything like this, but do you guys
know any similiar MS SQL ways to do this?

Mark






On Mon, 14 Mar 2005 07:41:30 -0500, Scott Marcus <marcus at tsstech.com> wrote:
> Would something like this work (at least it does on my computer)?
> 
> SELECT B.TheatreID, B.Movie, B.Tickets
> FROM tblBoxoffice AS B
> WHERE B.id In (SELECT TOP 30 A.id
> FROM tblBoxoffice AS A
> WHERE A.TheatreID=B.TheatreID
> ORDER BY A.TICKETS DESC)
> ORDER BY B.TheatreID, B.Tickets DESC;
> 
> Tables:
> 
> tblBoxoffice
>   id         autonumber
>   Movie      Text
>   Tickets    Long
>   TheatreID  Long
> 
> tblTheatre
>   id         autonumber
>   Theatre    Text
> 
> Scott Marcus
> TSS Technologies, Inc.
> marcus at tsstech.com
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Erwin Craps -
> IT Helps
> Sent: Monday, March 14, 2005 5:38 AM
> To: accessd at databaseadvisors.com
> Subject: [AccessD] TOP 30 for multiple theaters.
> 
> Hi group
> 
> Is it posible, and how do I do this to create a query with the folowing
> result.
> 
> Movietheatre X, movie 1, tickets, boxoffice
> Movietheatre X, movie 1, tickets, boxoffice
> Movietheatre X, movie 1, tickets, boxoffice
> Movietheatre X, movie 1, tickets, boxoffice
> Movietheatre X, movie 1, tickets, boxoffice
> 
> 
> Erwin Craps
> 
> Zaakvoerder
> 
> www.ithelps.be/onsgezin
> 
> This E-mail is confidential, may be legally privileged, and is for the
> intended recipient only. Access, disclosure, copying, distribution, or
> reliance on any of it by anyone else is prohibited and may be a criminal
> offence. Please delete if obtained in error and E-mail confirmation to
> the sender.
> 
> IT Helps - I.T. Help Center  ***  Box Office Belgium & Luxembourg
> 
> www.ithelps.be <http://www.ithelps.be/>   *  www.boxoffice.be
> <http://www.boxoffice.be/>   *  www.stadleuven.be
> <http://www.stadleuven.be/>
> 
> IT Helps bvba* ** Mercatorpad 3 **  3000 Leuven
> 
> IT Helps  *  Phone: +32 16 296 404  *  Fax: +32 16 296 405 E-mail:
> Info at ithelps.be
> 
> Box Office **  Fax: +32 16 296 406 **  Box Office E-mail:
> Staff at boxoffice.be <mailto:figures at boxoffice.be>
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>