Return a sequential record count for all records returned

From SQLZoo
Jump to navigation Jump to search

Return a sequential record count for all records returned

schema:scott

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 row_number() over(order by name),*
from bbc
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_bbc;
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
DataWars, Data Science Practice Projects - LogoDataWars: Practice Data Science/Analysis with +100 Real Life Projects