Search code examples
sqloracle-databaseoracle12c

SQL to generate a serial number from 1 to 9 repeatedly in oracle

I need generate serial number in Oracle SQL Query..

Example:

rownum (1,2..9,10,11..18,19,20..N)

my_srl_no (1,2..9,1,2..9,1,2..N)
Solution

  • What you are looking for is the modulo function MOD (https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/MOD.html#GUID-E12A3928-2C50-45B0-B8C3-82432C751B8C).

    If it's really rownum you want to deal with:

    mod(rownum - 1, 9) + 1

    but usually you would rather use ROW_NUMBER in order to number your rows by some sort criteria. Anyway, the math stays the same.