SQL Server Performance

help with row_number() over (partition

Discussion in 'SQL Server 2005 General Developer Questions' started by jassie, Feb 8, 2010.

  1. jassie New Member

    SELECT Region,grp1,gnum,cnt, RowNum
    FROM
    (
    SELECT Region,grp1,gnum,cnt, row_number() over (partition by grp1, gnum order by Cnt Desc) RowNum
    from (Region,grp1,gnum,count(*) as Cnt
    from dbo.tbl1
    group by Region,grp1,gnum)RDM
    )B
    WHERE
    RowNum <= 5
    ORDER BY 1,2,3,4
  2. FrankKalis Moderator

    Your question is not really precise.... [;)]
    But maybe this is what you're after?
    SELECT
    Region, grp1, gnum, cnt, RowNum
    FROM
    (SELECT
    Region, grp1, gnum, COUNT(*) AS cnt, ROW_NUMBER() OVER (PARTITION BY grp1, gnum ORDER BY COUNT(*) DESC) RowNum
    FROM
    dbo.tbl1
    GROUP BY Region, grp1, gnum) B
    WHERE
    RowNum <= 5
    ORDER BY
    1,2,3,4
    If not, please ask a question. [:)]
  3. Madhivanan Moderator

    [quote user="jassie"]
    SELECT Region,grp1,gnum,cnt, RowNum
    FROM
    (
    SELECT Region,grp1,gnum,cnt, row_number() over (partition by grp1, gnum order by Cnt Desc) RowNum
    from (Region,grp1,gnum,count(*) as Cnt
    from dbo.tbl1
    group by Region,grp1,gnum)RDM
    )B
    WHERE
    RowNum <= 5
    ORDER BY 1,2,3,4
    [/quote]
    How did you think that you gave us enough informations to help you? [;)]

Share This Page