Return a sequential record count for all records returned

Return a sequential record count for all records returned

schema:gisq

this should be simple - I would like to get a consecutive numbering count/id for each record returned from a query: eg: select * from tablex gives multiple rows like:

field1, field2, field3, ...
field1, field2, field3, ...
field1, field2, field3, ...

What I want is:

1, field1, field2, field3, ...
2, field1, field2, field3, ...
3, field1, field2, field3, ...
4, field1, field2, field3, ...

SELECT rownum, field1, field2, field3
  FROM TABLE_NAME;
SELECT rownum, field1, field2, field3
  FROM (SELECT field1, field2, field3
          FROM TABLE_NAME
         ORDER BY field1, field2, field3);

DROP TABLE numbered_bc;
CREATE TABLE numbered_bbc
  (counter INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
  ,name VARCHAR(50)
  ,region VARCHAR(60)
  ,area DECIMAL(10,0)
  ,population DECIMAL(11,0)
  ,gdp DECIMAL(14,0)
  );
INSERT INTO numbered_bbc (name, region, area, 
                          population,gdp)
SELECT name, region, area, population, gdp
  FROM gisq.bbc;
SELECT * FROM numbered_bbc

Use the psuedo-column ROWNUM. Note that ROWNUM is evaluation prior to the ORDER BY clause. To apply ROWNUM to an ordered result, use an inline view.

This should be simple - but it isn't. Here is a nasty work around... Copy the required table into another with auto-increment:

Meta Data

What are my tables?

What are the columns of the BBC table?

Get the first 10 rows of the gisq.cia table.

Get the 11th to the 20th rows of the cia table - by population.

What version of the software am I using?

What is the syntax to view structure of table?

How can you determine the primary key using SQL?

Return a sequential record count for all records returned