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:
ASKED:
January 11, 2008 11:15 PM
UPDATED:
February 22, 2008 5:42 AM
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.
Thanks!
No problem Frank.