Search code examples
sqloracle-databaserow-number

SELECT *, ROW_NUMBER() OVER in Oracle

Given a working example in T-SQL:

SELECT *, ROW_NUMBER() OVER (ORDER BY name ) as row_id
 FROM 
[schemaName].[Zoo]

If I keep * selector it throws following error ORA-00923: FROM keyword not found where expected.

Is there a way to keep * selector, and make it work in oracle pl/sql? (currently I could achieve wanted result only by mentioning all column names)

Solution

  • Try this:

    SELECT a.*, ROW_NUMBER() OVER (ORDER BY name ) as row_id FROM schemaName.Zoo a

    Here a is alias for table schemaName.Zoo. This will generate all columns from the original table, with row_id column added at the end.