Question

  Asked: Jan 11 2008   11:15 PM GMT
  Asked by: FrankKulash


ROW_NUMBER in SQLServer


SQL Server, ROW_NUMBER, SQL

In Oracle, I can use the analytic ROW_NUMBER function to assign sequence numbers to output rows, re-starting the sequence as certain values change (e.g., there's a separate count for each deptno in this example:)

SELECT deptno
, ename
, ROW_NUMBER () OVER
( PARTITION BY deptno
ORDER BY ename
) AS seq
FROM scott.emp
ORDER BY deptno
, ename;

Output:

DEPTNO ENAME SEQ
---------- ---------- ----------
10 CLARK 1
10 KING 2
10 MILLER 3
20 ADAMS 1
20 FORD 2
20 JONES 3
20 SCOTT 4
20 SMITH 5
30 ALLEN 1
30 BLAKE 2
30 JAMES 3
30 MARTIN 4
30 TURNER 5
30 WARD 6

14 rows selected.

Is there something similar in SQL Server?

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window

Answer Wiki (Improve, edit or add to this answer)


 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0



SQL Server 2005 introduced that same functionally using the same syntax.

If you are using SQL 2000 or prior there is no equivalent.
  • AddThis Social Bookmark Button

Browse more Questions and Answers on SQL Server, Oracle and Development.

Looking for relevant SQL Server Whitepapers? Visit the SearchSQLServer.com Research Library.


Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

Mrdenny  |   Jan 11 2008  11:55PM GMT

Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.

 


Mrdenny  |   Feb 22 2008  5:42AM GMT

No problem Frank.