CODEWITHSUNDEEP
sqlsql-serversql-order-byrow-number
jkh
jkh

Reputation: 3678

Multiple columns in OVER ORDER BY

Is there a way to specify multiple columns in the 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

Incorrect syntax near ','.

Upvotes: 11

Views: 56409

Answers (2)

We Are All Monica
We Are All Monica

Reputation: 13334

The problem is the extra parentheses around the column name. These should all 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

Also, when you ask an SQL question, you should always specify which database you are querying against.

Upvotes: 31

podiluska
podiluska

Reputation: 51494

No brackets.

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

Upvotes: 2

Related Questions