This is something to work from...
I threw it together from the Northwind DB that ships with SQL Server
SELECT
C1.CategoryName AS CATEGORY,
Count(C2.ProductID) AS 'TOTAL IN CATEGORY'
FROM
Categories C1, Products C2
WHERE
C1.CategoryID = C2.CategoryID
GROUP BY
C1.CategoryName
Mike
----- Original Message -----
From: "Jochem van Dieten" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Monday, July 28, 2003 11:21 AM
Subject: Re: Select TOP X form all Groups
> Ian Skinner wrote:
>
> > I need the top X records, if more then X records could be considered the
> > top, I just need X records based on any other order of convenience.
> >
> > For example:
> > NAME VIEWS
> > rec1 4
> > rec2 3
> > rec3 3
> > rec4 3
> >
> > In this case, I would want rec1 and any one of the following 3.
>
> Something like this should work:
>
> SELECT a.type, a.name, a.views
> FROM table a
> WHERE (
> SELECT COUNT()
> FROM table b
> WHERE a.type = b.type
> AND (
> b.views > a.views
> OR (
> b.views = a.views
> AND b.name < a.name
> )
> )
> ) < 2
>
> Jochem
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at
http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
