Difference between revisions of "Return a sequential record count for all records returned"

From SQLZOO
Jump to: navigation, search
 
Line 18: Line 18:
 
<source lang=sql class='tidy'>
 
<source lang=sql class='tidy'>
 
</source>
 
</source>
 +
<source lang=sql class='def e-mssql'>select row_number() over(order by name),*
 +
from bbc</source>
 
<source lang='sql' class='def e-oracle'>select rownum, field1, field2, field3
 
<source lang='sql' class='def e-oracle'>select rownum, field1, field2, field3
 
   from table_name;
 
   from table_name;

Latest revision as of 17:33, 1 July 2017

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