none
Dense_Rank() is ordering by the partition column

    Question

  • Hi all,

    I have been stuck on a particular use of the DENSE_RANK() function.

    I am trying to partition by a column but order by another column, but when I try to partition by a column SQL is ordering by the column i specified in the partition - very annoying! So here is my dataset:

    WITH dataSet AS (SELECT 'Apple' AS Fruit, CAST('20130101' AS DATE) AS EatenDateUNION ALLSELECT 'Apple', '20130102'UNION ALLSELECT 'Pear', '20130102'UNION ALLSELECT 'Pear', '20130103'UNION ALLSELECT 'Apple', '20130104'UNION ALLSELECT 'Apple', '20130105'UNION ALLSELECT 'Pear', '20130106'UNION ALLSELECT 'Apple', '20130107')SELECT *, DENSE_RANK() OVER(PARTITION BY FRUIT ORDER BY EATENDATE) AS Ranking FROM DataSet

    Here are the results

    Fruit         EatenDate Ranking
    Apple 2013-01-01 1
    Apple 2013-01-02 2
    Apple 2013-01-04 3
    Apple 2013-01-05 4
    Apple 2013-01-07 5
    Pear         2013-01-02 1
    Pear         2013-01-03 2
    Pear         2013-01-06 3

    but what i'm after is:

    Fruit EatenDate   Ranking
    Apple 2013-01-01 1
    Apple 2013-01-02 1
    Pear 2013-01-02 2
    Pear 2013-01-03 2
    Apple 2013-01-04 3
    Apple<span class="Apple-tab-span" style="white-spac

    Wednesday, November 20, 2013 4:38 PM

Answers

  • Hi,

    The result set is correct based on what you wrote.You partioned by Fruit column and Ordered by Date column.

    That Means With in the group of rows where fruit name is equal order the rows based on EatenDate Column

    ;WITH dataSet AS 
    (
    SELECT 'Apple' AS Fruit, CAST('20130101' AS DATE) AS EatenDate
    UNION ALL
    SELECT 'Apple', '20130102'
    UNION ALL
    SELECT 'Pear', '20130102'
    UNION ALL
    SELECT 'Pear', '20130103'
    UNION ALL
    SELECT 'Apple', '20130104'
    UNION ALL
    SELECT 'Apple', '20130105'
    UNION ALL
    SELECT 'Pear', '20130106'
    UNION ALL
    SELECT 'Apple', '20130107')
    SELECT *, DENSE_RANK() OVER(PARTITION BY FRUIT ORDER BY EATENDATE) AS Ranking 
    FROM DataSet

    Also For the output you wanted .You gave rank as 1 for first two rows.is that correct?


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

    Wednesday, November 20, 2013 4:59 PM
  • If I understand what you are looking for, it's not a dense rank, instead you want islands of fruit by date, then a dense rank of that.  So

    ;WITH dataSet AS (SELECT 'Apple' AS Fruit, CAST('20130101' AS DATE) AS EatenDate UNION ALL 
    SELECT 'Apple', '20130102'UNION ALL
    SELECT 'Pear', '20130102'UNION ALL
    SELECT 'Pear', '20130103'UNION ALL
    SELECT 'Apple', '20130104'UNION ALL
    SELECT 'Apple', '20130105'UNION ALL
    SELECT 'Pear', '20130106'UNION ALL
    SELECT 'Apple', '20130107'),
    Islands As
    (Select Fruit, EatenDate, 
       ROW_NUMBER() Over (Order By EatenDate, Fruit) - ROW_NUMBER() Over (Partition By Fruit Order By EatenDate) As Island
    From dataset),
    MinDates As
    (Select Fruit, EatenDate, Island, MIN(EatenDate) Over(Partition By Fruit, Island) As MinDate
    From Islands)
    Select Fruit, EatenDate, DENSE_RANK() OVER(Order By Mindate, Fruit) As Ranking
    From MinDates
    Order By Ranking, MinDate, Fruit;

    Tom

    Wednesday, November 20, 2013 5:37 PM

All replies

  • Hi all,

    I have been stuck on a particular use of the DENSE_RANK() function.

    I am trying to partition by a column but order by another column, but when I try to partition by a column SQL is ordering by the column i specified in the partition - very annoying! So here is my dataset:

    WITH dataSet AS (SELECT 'Apple' AS Fruit, CAST('20130101' AS DATE) AS EatenDateUNION ALLSELECT 'Apple', '20130102'UNION ALLSELECT 'Pear', '20130102'UNION ALLSELECT 'Pear', '20130103'UNION ALLSELECT 'Apple', '20130104'UNION ALLSELECT 'Apple', '20130105'UNION ALLSELECT 'Pear', '20130106'UNION ALLSELECT 'Apple', '20130107')SELECT *, DENSE_RANK() OVER(PARTITION BY FRUIT ORDER BY EATENDATE) AS Ranking FROM DataSet

    Here are the results

    Fruit         EatenDate Ranking
    Apple 2013-01-01 1
    Apple 2013-01-02 2
    Apple 2013-01-04 3
    Apple 2013-01-05 4
    Apple 2013-01-07 5
    Pear         2013-01-02 1
    Pear         2013-01-03 2
    Pear         2013-01-06 3

    but what i'm after is:

    Fruit EatenDate   Ranking
    Apple 2013-01-01 1
    Apple 2013-01-02 1
    Pear 2013-01-02 2
    Pear 2013-01-03 2
    Apple 2013-01-04 3
    Apple<span class="Apple-tab-span" style="white-spac

    Wednesday, November 20, 2013 4:55 PM
  • Hi,

    The result set is correct based on what you wrote.You partioned by Fruit column and Ordered by Date column.

    That Means With in the group of rows where fruit name is equal order the rows based on EatenDate Column

    ;WITH dataSet AS 
    (
    SELECT 'Apple' AS Fruit, CAST('20130101' AS DATE) AS EatenDate
    UNION ALL
    SELECT 'Apple', '20130102'
    UNION ALL
    SELECT 'Pear', '20130102'
    UNION ALL
    SELECT 'Pear', '20130103'
    UNION ALL
    SELECT 'Apple', '20130104'
    UNION ALL
    SELECT 'Apple', '20130105'
    UNION ALL
    SELECT 'Pear', '20130106'
    UNION ALL
    SELECT 'Apple', '20130107')
    SELECT *, DENSE_RANK() OVER(PARTITION BY FRUIT ORDER BY EATENDATE) AS Ranking 
    FROM DataSet

    Also For the output you wanted .You gave rank as 1 for first two rows.is that correct?


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

    Wednesday, November 20, 2013 4:59 PM
  • Hi,

    This is duplicate thread..

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6d90e8c1-705f-43ed-a67f-508561b5e63e/denserank-is-ordering-by-the-partition-column?forum=transactsql


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

    Wednesday, November 20, 2013 5:31 PM
  • If I understand what you are looking for, it's not a dense rank, instead you want islands of fruit by date, then a dense rank of that.  So

    ;WITH dataSet AS (SELECT 'Apple' AS Fruit, CAST('20130101' AS DATE) AS EatenDate UNION ALL 
    SELECT 'Apple', '20130102'UNION ALL
    SELECT 'Pear', '20130102'UNION ALL
    SELECT 'Pear', '20130103'UNION ALL
    SELECT 'Apple', '20130104'UNION ALL
    SELECT 'Apple', '20130105'UNION ALL
    SELECT 'Pear', '20130106'UNION ALL
    SELECT 'Apple', '20130107'),
    Islands As
    (Select Fruit, EatenDate, 
       ROW_NUMBER() Over (Order By EatenDate, Fruit) - ROW_NUMBER() Over (Partition By Fruit Order By EatenDate) As Island
    From dataset),
    MinDates As
    (Select Fruit, EatenDate, Island, MIN(EatenDate) Over(Partition By Fruit, Island) As MinDate
    From Islands)
    Select Fruit, EatenDate, DENSE_RANK() OVER(Order By Mindate, Fruit) As Ranking
    From MinDates
    Order By Ranking, MinDate, Fruit;

    Tom

    Wednesday, November 20, 2013 5:37 PM
  • Hi all,

    I have been stuck on a particular use of the DENSE_RANK() function.

    I am trying to partition by a column but order by another column, but when I try to partition by a column SQL is ordering by the column i specified in the partition - very annoying! So here is my dataset:

    WITH dataSet AS (SELECT 'Apple' AS Fruit, CAST('20130101' AS DATE) AS EatenDateUNION ALLSELECT 'Apple', '20130102'UNION ALLSELECT 'Pear', '20130102'UNION ALLSELECT 'Pear', '20130103'UNION ALLSELECT 'Apple', '20130104'UNION ALLSELECT 'Apple', '20130105'UNION ALLSELECT 'Pear', '20130106'UNION ALLSELECT 'Apple', '20130107')SELECT *, DENSE_RANK() OVER(PARTITION BY FRUIT ORDER BY EATENDATE) AS Ranking FROM DataSet

    Here are the results

    Fruit     EatenDate     Ranking
    Apple    2013-01-01    1
    Apple    2013-01-02    2
    Apple    2013-01-04    3
    Apple    2013-01-05    4
    Apple    2013-01-07    5
    Pear     2013-01-02    1
    Pear     2013-01-03    2
    Pear     2013-01-06    3

    but what i'm after is:

    Fruit    EatenDate      Ranking
    Apple  2013-01-01   1
    Apple  2013-01-02   1
    Pear    2013-01-02   2
    Pear    2013-01-03   2
    Apple   2013-01-04   3
    Apple   2013-01-05   3
    Pear    2013-01-06    4
    Apple   2013-01-07    5


    My workaround is (I know, its very long winded):

    Thursday, November 21, 2013 10:48 AM
  • Can you explain in words whats your rule for getting dense rank as above? on what basis you want to rank?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, November 21, 2013 11:39 AM
  • Your workaround is very short!

    I don't understand what logic you are using to get the expected result, unless in your edit you made a typo and actually want

    Fruit EatenDate  Ranking
    ----- ---------- --------------------
    Apple 2013-01-01 1
    Apple 2013-01-02 2
    Pear  2013-01-02 1
    Pear  2013-01-03 2
    Apple 2013-01-04 3
    Apple 2013-01-05 4
    Pear  2013-01-06 3
    Apple 2013-01-07 5

    This can achieved by simply ordering the resulting data set

    with    dataSet
              as ( select   'Apple' as Fruit ,
                            cast('20130101' as date) as EatenDate
                   union all
                   select   'Apple' ,
                            '20130102'
                   union all
                   select   'Pear' ,
                            '20130102'
                   union all
                   select   'Pear' ,
                            '20130103'
                   union all
                   select   'Apple' ,
                            '20130104'
                   union all
                   select   'Apple' ,
                            '20130105'
                   union all
                   select   'Pear' ,
                            '20130106'
                   union all
                   select   'Apple' ,
                            '20130107'
                 )
        select  * ,
                dense_rank() over ( partition by FRUIT order by EATENDATE ) as Ranking
        from    DataSet
    	order by Ranking

    Thursday, November 21, 2013 1:26 PM
  • This is a duplicate of http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6d90e8c1-705f-43ed-a67f-508561b5e63e/denserank-is-ordering-by-the-partition-column?forum=transactsql

    Shaheen, please do not ask the same question multiple times.  It is most efficient if you ask it once.  If the replies you get do not give you the result you need, please do a reply to explain why the responses are not working for you.

    Tom

    P.S., thank you for providing sample data and the result you want from that sample data, that is very helpful.

    Thursday, November 21, 2013 1:32 PM
  • Hi all,

    I have been stuck on a particular use of the DENSE_RANK() function.

    I am trying to partition by a column but order by another column, but when I try to partition by a column SQL is ordering by the column i specified in the partition - very annoying! So here is my dataset:

    WITH dataSet AS (SELECT 'Apple' AS Fruit, CAST('20130101' AS DATE) AS EatenDateUNION ALLSELECT 'Apple', '20130102'UNION ALLSELECT 'Pear', '20130102'UNION ALLSELECT 'Pear', '20130103'UNION ALLSELECT 'Apple', '20130104'UNION ALLSELECT 'Apple', '20130105'UNION ALLSELECT 'Pear', '20130106'UNION ALLSELECT 'Apple', '20130107')SELECT *, DENSE_RANK() OVER(PARTITION BY FRUIT ORDER BY EATENDATE) AS Ranking FROM DataSet

    Here are the results

    Fruit     EatenDate     Ranking
    Apple    2013-01-01    1
    Apple    2013-01-02    2
    Apple    2013-01-04    3
    Apple    2013-01-05    4
    Apple    2013-01-07    5
    Pear     2013-01-02    1
    Pear     2013-01-03    2
    Pear     2013-01-06    3

    but what i'm after is:

    Fruit    EatenDate      Ranking
    Apple  2013-01-01   1
    Apple  2013-01-02   1
    Pear    2013-01-02   2
    Pear    2013-01-03   2
    Apple   2013-01-04   3
    Apple   2013-01-05   3
    Pear    2013-01-06    4
    Apple   2013-01-07    5


    My workaround is (I know, its very long winded):

    
    

    ;WITH dataSet AS (SELECT 'Apple' AS Fruit, CAST('20130101' AS DATE) AS EatenDate
    UNION ALL
    SELECT 'Apple', '20130102'
    UNION ALL
    SELECT 'Pear', '20130102'
    UNION ALL
    SELECT 'Pear', '20130103'
    UNION ALL
    SELECT 'Apple', '20130104'
    UNION ALL
    SELECT 'Apple', '20130105'
    UNION ALL
    SELECT 'Pear', '20130106'
    UNION ALL
    SELECT 'Apple', '20130107')
     
     --Query for your question
     ,mycte1 AS(
     
    SELECT Fruit, EatenDate
    ,row_number() Over(Order by EatenDate,Fruit) - row_number() Over(Partition by Fruit Order by EatenDate,Fruit DESC) rnDelta
    FROM dataSet
    )  
    ,mycte2 as (
    Select EatenDate,Fruit 
     ,row_number() Over(Order by EatenDate,Fruit) - row_number() Over(Partition by Fruit, rnDelta Order by rnDelta, EatenDate, Fruit DESC) rnDelta2
     FROM mycte1
    )
    SELECT Fruit, EatenDate, DENSE_RANK()  OVER(ORDER BY rnDelta2) AS Ranking 
     FROM  mycte2
    Order by EatenDate,Fruit

    Thursday, November 21, 2013 5:32 PM
    Moderator