jcobo1
jcobo1

Reputation: 1180

Set auto-increment field on Oracle select sql statement

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

Answers (2)

jcobo1
jcobo1

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

Lukasz Szozda
Lukasz Szozda

Reputation: 176104

Seems to me you search for ROW_NUMBER:

SELECT *, ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ... ) AS rn
FROM your_tab

LiveDemo

Upvotes: 5

Related Questions