Return a sequential record count for all records returned

From SQLZOO
Revision as of 13:43, 17 July 2012 by Connor (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, 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 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
 
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense