ROW_NUMBER in SQLServer
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?
Software/Hardware used:
Software/Hardware used:
ASKED:
January 11, 2008 11:15 PM
UPDATED:
February 22, 2008 5:42 AM
Answer Wiki:
SQL Server 2005 introduced that same functionally using the same syntax.
If you are using SQL 2000 or prior there is no equivalent.
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.
Thanks!
No problem Frank.