Recipe 1.27. Numbering Query Output Lines
ProblemYou'd like the lines of a query result nicely numbered. SolutionPostprocess the output from mysql or use a user-defined variable. DiscussionThe --skip-column-names option for mysql can be useful in combination with cat -n when you want to number the output rows from a query under Unix: % mysql --skip-column-names -e "SELECT thing, arms FROM limbs" cookbook | cat -n 1 human 2 2 insect 0 3 squid 10 4 octopus 8 5 fish 0 6 centipede 0 7 table 0 8 armchair 2 9 phonograph 1 10 tripod 0 11 Peg Leg Pete 2 12 NULL Another option is to use a user variable. Expressions involving variables are evaluated for each row of a query result, a property that you can use to provide a column of row numbers in the output: mysql> SET @n = 0; mysql> SELECT @n := @n+1 AS rownum, thing, arms, legs FROM limbs; +--------+--------------+------+------+ | rownum | thing | arms | legs | +--------+--------------+------+------+ | 1 | human | 2 | 2 | | 2 | insect | 0 | 6 | | 3 | squid | 10 | 0 | | 4 | octopus | 8 | 0 | | 5 | fish | 0 | 0 | | 6 | centipede | 0 | 100 | | 7 | table | 0 | 4 | | 8 | armchair | 2 | 4 | | 9 | phonograph | 1 | 0 | | 10 | tripod | 0 | 3 | | 11 | Peg Leg Pete | 2 | 1 | | 12 | space alien | NULL | NULL | +--------+--------------+------+------+ |
