How do I select all the columns from a table, plus additional columns like ROWNUM?

SqlOracleSelect

Sql Problem Overview

In Oracle, it's possible to do a SELECT statement that returns the row number as a column in your result set.

For example,

SELECT rownum, column1, column2 FROM table

returns:

rownum       column1       column2
1            Joe           Smith
2            Bob           Jones

But I don't want to specify each column by hand. I want to do something like:

select rownum,* from table


rownum       column1       column2       column3       column4
1            Joe           Smith         1             2
2            Bob           Jones         3             4

Any ideas?

Sql Solutions

Solution 1 - Sql

Qualify the * with the name of the table:

select rownum, table.* from table

Solution 2 - Sql

Dave's answer is great, i'd just like to add that it's also possible to do that by placing the wildcard as the first column:

select *,rownum from table

Works, but the following won't:

select rownum,* from table

I've tested on MySQL.

Solution 3 - Sql

Dave's answer did not work for me on Oracle 11g (table.* without alias). The following worked:

select rownum, t.* from table t

Solution 4 - Sql

Unfortuantely, i dont think therei s a way to do it, easiest is probably inner join with itself with an inline table of id,count(*), and put an outer select statement

Categories
Recommended Sql Solutions
Recommended Oracle Solutions
Recommended Select Solutions

Previous Solution:

S3 limit to objects in a bucket

Amazon S3Amazon Web-Services

Next Solution:

JUnit4 fail() is here, but where is pass()?

JavaJunit

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionntsueView Question on Stackoverflow
Solution 1 - SqlDave CostaView Answer on Stackoverflow
Solution 2 - SqlgaborousView Answer on Stackoverflow
Solution 3 - SqlPierre CattinView Answer on Stackoverflow
Solution 4 - SqlZiyi WangView Answer on Stackoverflow