All geek questions in one place

Multiple columns in OVER ORDER BY

Is there a way to specify multiple columns in an OVER ORDER BY clause?

SELECT ROW_NUMBER() OVER(ORDER BY (A.Col1)) AS ID FROM MyTable A

The above works fine, but trying to add a second column does not work.

 SELECT ROW_NUMBER() OVER(ORDER BY (A.Col1, A.Col2)) AS ID FROM MyTable A

Invalid syntax next to ','.

+6
source share
3 answers

The problem is the extra parentheses around the column name. All should work:

 -- The standard way SELECT ROW_NUMBER() OVER(ORDER BY A.Col1) AS ID FROM MyTable A SELECT ROW_NUMBER() OVER(ORDER BY A.Col1, A.Col2) AS ID FROM MyTable A -- Works, but unnecessary SELECT ROW_NUMBER() OVER(ORDER BY (A.Col1), (A.Col2)) AS ID FROM MyTable A

In addition, when you ask an SQL question, you should always indicate which database you are querying.

+13
source

Without brackets.

 SELECT ROW_NUMBER() OVER(ORDER BY A.Col1, A.Col2) AS ID FROM MyTable A
0
source

it's impossible you can see the syntax of Row_Num :

 ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

if you use an additional order registered in the group, you can do it.

-5
source

Source: https://habr.com/ru/post/921501/

More articles:


All Articles