11.19.1 Problem
You want to number query output rows.
11.19.2 Solution
If you're writing your own program, just add the row number yourself.
11.19.3 Discussion
A type of sequence that has nothing to do with the contents of your database is to number output rows from a query. When working within an API, you can number the rows by maintaining a counter and displaying its current value with each row's contents. Here is an example in Python, using the insects table. It simply displays a numbered list of the distinct values in the origin column of the table:
cursor = conn.cursor ( ) cursor.execute ("SELECT DISTINCT origin FROM insect") count = 1 while 1: row = cursor.fetchone ( ) if row == None: break print count, row[0] count = count + 1 cursor.close ( )
11.19.4 See Also
The mysql program provides no explicit row-numbering facilities, although you can use a SQL variable to include an extra row number column in a query's output. Another way to produce results that may be suitable for your purposes is to filter mysql output through another program that adds row numbers. These techniques are described in Recipe 1.27.
Using the mysql Client Program
Writing MySQL-Based Programs
Record Selection Techniques
Working with Strings
Working with Dates and Times
Sorting Query Results
Generating Summaries
Modifying Tables with ALTER TABLE
Obtaining and Using Metadata
Importing and Exporting Data
Generating and Using Sequences
Using Multiple Tables
Statistical Techniques
Handling Duplicates
Performing Transactions
Introduction to MySQL on the Web
Incorporating Query Resultsinto Web Pages
Processing Web Input with MySQL
Using MySQL-Based Web Session Management
Appendix A. Obtaining MySQL Software
Appendix B. JSP and Tomcat Primer
Appendix C. References