Post reply

How to correct the query and make it working in 2016

  • January 8, 2020 at 3:48 am

    Go to Answer

    #3713478 

    WITH T1 (ID1, ID2)
    AS
    (
    SELECT 1,1 UNION ALL
    SELECT 1,1 UNION ALL
    SELECT 1,1 UNION ALL
    SELECT 1,2
    )
    SELECT *,
    RANK() OVER (PARTITION BY ID1, ORDER BY ID2) AS 'RANK',
    ROW_NUMBER() OVER (PARTITION BY ID1, ORDER BY ID2) AS 'ROW_NUMBER',
    DENSE_RANK() OVER (PARTITION BY ID1, ORDER BY ID2) AS 'DENSE_RANK'
    FROM T1

     

    ERROR: Msg 156, Level 15, State 1, Line 10

    Incorrect syntax near the keyword 'ORDER'.

    This is a query executed and errors in 2008R2 (I don't have higher version in my local environment) , maybe it works on higher edition? Can anyone help to run it on 2016 please?

    Thank you very much.

    • This topic was modified 4 years, 10 months ago by  PasLe Choix.

  • January 8, 2020 at 4:35 am

    Answer

    #3713482

    Your problem is the "," before the "ORDER BY" in your "OVER" clauses

    WITH T1 (ID1, ID2)
    AS
    (
    SELECT 1,1 UNION ALL
    SELECT 1,1 UNION ALL
    SELECT 1,1 UNION ALL
    SELECT 1,2
    )
    SELECT *,
    RANK() OVER (PARTITION BY ID1 ORDER BY ID2) AS 'RANK',
    ROW_NUMBER() OVER (PARTITION BY ID1 ORDER BY ID2) AS 'ROW_NUMBER',
    DENSE_RANK() OVER (PARTITION BY ID1 ORDER BY ID2) AS 'DENSE_RANK'
    FROM T1

    How to post data/code on a forum to get the best help.

  • January 8, 2020 at 5:07 am

    #3713496

    Thank you DesNorton for catching that.

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

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