Multiple columns in OVER ORDER BY

sql sql-server sql-order-by row-number
jkh picture jkh · Jul 27, 2012 · Viewed 34.3k times · Source

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 ','.

Answer

We Are All Monica picture We Are All Monica · Jul 27, 2012

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.

Related questions

SQL ROW_NUMBER and sorting issue

In SQL 2005/2008 database we have table BatchMaster. Columns: RecordId bigint - autoincremental id, BatchNumber bigint - unique non-clustered index, BatchDate). We have sproc that returns paginated data from this table. That sproc works fine for most of the clients, but …

Read More
How to update and order by using ms sql

Ideally I want to do this: UPDATE TOP (10) messages SET status=10 WHERE status=0 ORDER BY priority DESC; In English: I want to get the top 10 available (status=0) messages from the DB and lock them (status=10). A message with a higher …

Read More
Combining ORDER BY AND UNION in SQL Server

How can I get first record of a table and last record of a table in one result-set? This Query fails SELECT TOP 1 Id,Name FROM Locations ORDER BY Id UNION ALL SELECT TOP 1 Id,Name FROM Locations ORDER BY …

Read More