SQL Server Performance

Dense Rank Startg Number

Discussion in 'SQL Server 2008 General Developer Questions' started by Brinda, Dec 15, 2010.

  1. Brinda New Member

    Hi,
    I have used the below query for the getting different dense rank
    select ROW_NUMBER() OVER (ORDER BY column2,column84,column21) ID,
    RANK() OVER (
    ORDER BY Level_Zero.column2) AS 'Rank',
    DENSE_RANK() OVER (
    ORDER BY Level_Zero.column2) AS denserank,
    DENSE_RANK() OVER (
    ORDER BY Level_Zero.column2,Level_zero.column84) AS denserank1,
    DENSE_RANK() OVER (
    ORDER BY Level_Zero.column2,Level_zero.column84,Level_zero.column21) AS denserank2,
    from #temp
    and the output of this is
    IDDenseRankDenseRank1DenseRank2
    1111 AkronCleveland OH2007-QTR 12007-Feb0
    2111 AkronCleveland OH2007-QTR 12007-Jan0
    3111 AkronCleveland OH2007-QTR 12007-Mar0
    4112 AkronCleveland OH2007-QTR 22007-Apr0
    5112 AkronCleveland OH2007-QTR 22007-Jun0
    6112 AkronCleveland OH2007-QTR 22007-May0
    7223 AllenDallas-Fort Worth TX2009-QTR 12009-Feb3652But i want a output likeIDDenseRankDenseRank1 DenseRank2
    1135 AkronCleveland OH2007-QTR 12007-Feb0
    2135 AkronCleveland OH2007-QTR 12007-Jan0
    3135 AkronCleveland OH2007-QTR 12007-Mar0
    4136 AkronCleveland OH2007-QTR 22007-Apr0
    5136 AkronCleveland OH2007-QTR 22007-Jun0
    6136 AkronCleveland OH2007-QTR 22007-May0
    724 7 AllenDallas-Fort Worth TX2009-QTR 12009-Feb3652That is dense rank of column1 is to be continued in the next column
    Is that possible..Kindly help me out
    Brinda, Dec 15, 2010
    #2

  2. FrankKalis Moderator

    Have you found a workaround or a solution to this issue?
    FrankKalis, Jan 11, 2011
    #3

  3. Madhivanan Moderator

    If you did not find an answer, here is the starter

    select name,
    dense_rank() over (order by name) as dense_rank1,
    dense_rank() over (order by name) as dense_rank2,
    dense_rank() over (order by name) as dense_rank3
    from
    (
    select 'a' as name union all
    select 'a' as name union all
    select 'b' as name union all
    select 'c' as name union all
    select 'c' as name union all
    select 'd' as name
    ) as t

    select name,
    dense_rank1,
    max(dense_rank1) over ()+ dense_rank() over (order by name) as dense_rank2,
    max(dense_rank2) over ()+ dense_rank() over (order by name) as dense_rank3
    from
    (
    select name,
    dense_rank() over (order by name) as dense_rank1,
    dense_rank() over (order by name) + dense_rank() over (order by name) as dense_rank2
    from
    (
    select 'a' as name union all
    select 'a' as name union all
    select 'b' as name union all
    select 'c' as name union all
    select 'c' as name union all
    select 'd' as name
    ) as t
    ) t
    Madhivanan, Jan 28, 2011
    #4

Share This Page