Question

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?

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

If you are using SQL 2000 or prior there is no equivalent.