Reputation: 1180
I've got one sql select statement on Oracle database where I need to add an auto-increment fields (on the fetched records, not on the database). I've been trying with some responses I've found on stackoverflow: How to generate auto increment field in select query
But seems not work. There is for any limitation on Oracle? Anyone know something? Would be great if autoincrements goes 100 on 100.
Thank you
Solved:
SELECT A, B, (ROW_NUMBER() OVER(PARTITION BY 100 ORDER BY FDAAID)*100) AS C
FROM SOME_TABLE
WHERE A > 15;
With *100 counter is going from 100 to 100.
Upvotes: 2
Views: 9263
Reputation: 1180
Solved:
SELECT A, B, (ROW_NUMBER() OVER(PARTITION BY 100 ORDER BY FDAAID)*100) AS C
FROM SOME_TABLE
WHERE A > 15;
With *100 counter is going from 100 to 100.
Upvotes: 2
Reputation: 176104
Seems to me you search for ROW_NUMBER
:
SELECT *, ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ... ) AS rn
FROM your_tab
Upvotes: 5