SELECT *, ROW_NUMBER() OVER in Oracle

sql oracle row-number
meta4 picture meta4 · Jul 25, 2016 · Viewed 11.2k times · Source

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)

Answer

Sanjay Radadiya picture Sanjay Radadiya · Jul 25, 2016

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.

Related questions

Oracle 'Partition By' and 'Row_Number' keyword

I have a SQL query written by someone else and I'm trying to figure out what it does. Can someone please explain what the Partition By and Row_Number keywords does here and give a simple example of it in …

Read More
SQL Query With Row_Number, order by and where clause

I have the following SQL query: select      ID, COLUMN1, COLUMN2 from      (select ID, COLUMN1, COLUMN2, row_number() over (order by 2 DESC) NO from A_TABLE) where      NO between 0 and 100 What I am trying to do is to select the first 100 …

Read More
How can I speed up row_number in Oracle?

I have a SQL query that looks something like this: SELECT * FROM( SELECT ..., row_number() OVER(ORDER BY ID) rn FROM ... ) WHERE rn between :start and :end Essentially, it's the ORDER BY part that's slowing things down. If I were …

Read More