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

From SQLZOO
Jump to: navigation, search
(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 ...")
 
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 numbered_bc;
+
<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

Revision as of 13:43, 17 July 2012

Return a sequential record count for all records returned

schema:gisq

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