Hi Ian,
Can you try this query:
SELECT Type, Name, Views, (type + name)
FROM dbo.Your_Table a
WHERE type + name IN
(SELECT TOP 2 type + Name
FROM dbo.Your_Table b
WHERE a.type = b.type
ORDER BY views DESC)
ORDER BY type, views DESC
----------------------------------------------------
Mario
-----Original Message-----
From: Michael T. Tangorre [mailto:[EMAIL PROTECTED]
Sent: Monday, July 28, 2003 11:31 AM
To: CF-Talk
Subject: Re: Select TOP X form all Groups
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
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
