Row_Number() OVER (PARTITION BY.....)

Comments

8 comments

  • Avatar
    Sage

    Hi Gregory,

    Row_Number() is not supported in the SiSense ElastiCube Manager.  One way to fake it is to create a custom field with the same value for all rows (the number 1 for example).  You would then reference this field as a parameter for RankAsc().

    This would have roughly the same effect.  You may need to perform further SQL manipulations to achieve the desired result.

    Thanks,

    Sage

    0
    Comment actions Permalink
  • Avatar
    Gregory Sloan

    Actually, RankAsc() did the trick (already had the fields in the table I needed to partition by, no need to create an extra).

     

    Thanks Sage

    0
    Comment actions Permalink
  • Avatar
    Yoav

    Hi Gregory,

     

    You can actually achieve Row_Number() OVER (PARTITION BY.....) by using this syntax:

    RANKASC([partitionA],[paritionB]...[partitionN],[orderByField])

    ie. RANKASC([EmployeeID],[CategoryID],[Quantity])

     

    Same applies for RANKDESC of course...

     

    Let us know if you need any more help.

     

    Regards,

    Yoav

     

    0
    Comment actions Permalink
  • Avatar
    Joe McClure

    What about when you need certain fields descending within the RANKASC()?

    0
    Comment actions Permalink
  • Avatar
    Muthusamy Jayaraman

    I am trying to use RANKDESC(id, modified_on).

    I have two records for an id say 829 with different modified on date field.

    But, this gives rank 1 for both the records.

    I have created a support ticket but check in the community as well if i am doing anything wrong.

     

    We are using Sisense 7.1 in Windows server 2016.

     

     

    1
    Comment actions Permalink
  • Avatar
    Muthusamy Jayaraman

    Ref image:

    0
    Comment actions Permalink
  • Avatar
    Muthusamy Jayaraman

    Tried to workaround using the group by sql as i just need the top one.

    SELECT
    a.issue_id,
    max(CASE WHEN a.modified_on IS NULL THEN CreateDate(1900,01,01) ELSE a.modified_on end) modified_on
    FROM [issue] a
    GROUP BY a.issue_id
    ORDER BY a.issue_id, modified_on desc

     

    Here, also i am getting both the records and the group by is not working.

    0
    Comment actions Permalink
  • Avatar
    Gowtham Senthilkumar

    Hi Muthusamy,

    Please check the arguments that are inside the "RankAsc(<fields>". 

    If you include your key of the table inside the RankAsc, then you'd get 1's all over. So, the solution here would be to create a dummy column with the same value - for (ex - create a column "Dummy" with the value 1 throughout)  and then run the RankAsc([Dummy]) to get the rank. 

    We also have a function of Rank() which will give you the row number directly. 

     

    Thanks, 

    Gowtham Senthilkumar

    0
    Comment actions Permalink

Please sign in to leave a comment.