Post reply

Alternative for row_number() over (partition by Department order by Salary desc)

  • April 17, 2019 at 10:56 am

    #3423901

    Hi,

     

    I am looking for the alternative code for "row_number() over (partition by <<table_name>> order by <<field>> desc)" using join conditions. Can any one help me to on this part?

  • April 17, 2019 at 11:00 am

    #3423949

    Could you elaborate here? Why does ROW_NUMBER not serve the requirement? You could use COUNT with a ROWS BETWEEN clause, but why reinvent the wheel?

    SELECT [name],
           ROW_NUMBER() OVER (PARTITION BY schema_id ORDER BY [name]) AS RN,
           COUNT(object_ID) OVER (PARTITION BY schema_id ORDER BY [name]
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cRN
    FROM sys.tables;

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • April 17, 2019 at 11:09 am

    #3424070

    Hi Thom,

    The database which I am currently working is not supporting the ROW_NUMBER() OVER (PARTITION BY schema_id ORDER BY [name])  clause. so I am checking any alternatives to these functions using the join clause.

    Thanks,

    Binu

  • April 17, 2019 at 11:18 am

    #3424226

    binumon.vijayan wrote:

    Hi Thom, The database which I am currently working is not supporting the ROW_NUMBER() OVER (PARTITION BY schema_id ORDER BY [name])  clause. so I am checking any alternatives to these functions using the join clause. Thanks, Binu

    What version are you using? ROW_NUMBER was added in SQL Server 2005 (if i recall correctly), which would mean you're running SQL Server 2000 or older. You've posted in the SQL Server 2012 forum, and ROW_NUMBER most definitely works in that version. Perhaps the problem isn't ROW_NUMBER but another syntax error?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

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

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