Difference between revisions of "Return a sequential record count for all records returned"
From SQLZOO
(Created page with "Return a sequential record count for all records returned <div class='ht'> <div class=params>schema:gisq</div> <div> this should be simple - I would like to get a consecutive ...") |
|||
| (One intermediate revision by one user not shown) | |||
| 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: | ||
| Line 25: | Line 25: | ||
order by field1, field2, field3); | order by field1, field2, field3); | ||
</source> | </source> | ||
| − | <source lang='sql' class='def e-mysql'>DROP TABLE | + | <source lang='sql' class='def e-mysql'>DROP TABLE numbered_bbc; |
CREATE TABLE numbered_bbc | CREATE TABLE numbered_bbc | ||
(counter INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY | (counter INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY | ||
Latest revision as of 12: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
