Post reply

Get top N for item in select

  • September 12, 2005 at 2:41 pm

    #91075

    Hope someone can help.  I am trying to write a query where I pull back the top 20 rows for an item in the select list.  As an example, I need to return the top 20 clients (by fees) for each year in which there is data.  I was hoping to do something besides a union between each year of data. So if there are 5 years of data, I would end up with 100 rows.

    SELECT top 20 cyear, client, sum(fees) as fees from tt1

    where cyear='2005'

    order by fees DESC

    TIA,

    Beth

  • September 12, 2005 at 2:49 pm

    #589178

    SELECT O.XType

    , O.name

    FROM dbo.SysObjects O

    WHERE ID IN (SELECT TOP 90 PERCENT ID FROM dbo.SysObjects O2 WHERE O2.XType = O.XType order by O2.Name)

    ORDER BY O.XType, O.Name

  • September 12, 2005 at 7:04 pm

    #589215

    Beth - you may be looking for something like this...

    SELECT cyear, client, SUM(fees)
FROM tt1 A
WHERE client IN
(SELECT TOP 20 client FROM tt1 B WHERE A.cyear = B.cyear
GROUP BY cyear, client ORDER BY cyear, SUM(fees) DESC)
GROUP BY cyear, client
ORDER BY cyear, sum(fees) DESC







    **ASCII stupid question, get a stupid ANSI !!!**

  • September 15, 2005 at 9:30 am

    #590191

    Thanks to you both very much, worked like a charm!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply