Numbering Query Output Rows Sequentially
Problem
You want to number query output rows.
Solution
If you’re writing your own program, just add the row number 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 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 ( )
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.
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.