MySQL Cookbook

Problem

You want to number the rows of a query result.

Solution

If you're writing your own program, just add the row numbers yourself.

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 displays a simple 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 for row in cursor.fetchall (): print count, row[0] count = count + 1 cursor.close ()

See Also

The mysql program provides no explicit row-numbering facilities, although you can use a user-defined 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. Section 1.27 describes these techniques.