Difference between revisions of "Return a sequential record count for all records returned"
From SQLZOO
| Line 1: | Line 1: | ||
Return a sequential record count for all records returned | Return a sequential record count for all records returned | ||
<div class='ht'> | <div class='ht'> | ||
| − | <div class=params>schema: | + | <div class=params>schema:scott</div> |
<div> | <div> | ||
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: | 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: | ||
Latest revision as of 13:43, 17 July 2012
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
Return a sequential record count for all records returned
