Row Rank in a MySQL View Row Rank in a MySQL View sql sql

Row Rank in a MySQL View


SELECT,       t.variety,       (SELECT COUNT(*) FROM TABLE WHERE id < +1 AS NUM  FROM TABLE t

It's not an ideal manner of doing this, because the query for the num value will execute for every row returned. A better idea would be to create a NUMBERS table, with a single column containing a number starting at one that increments to an outrageously large number, and then join & reference the NUMBERS table in a manner similar to the variable example that follows.

MySQL Ranking, or Lack Thereof

You can define a variable in order to get psuedo row number functionality, because MySQL doesn't have any ranking functions:

SELECT,       t.variety,       @rownum := @rownum + 1 AS num  FROM TABLE t,       (SELECT @rownum := 0) r
  • The SELECT @rownum := 0 defines the variable, and sets it to zero.
  • The r is a subquery/table alias, because you'll get an error in MySQL if you don't define an alias for a subquery, even if you don't use it.

Can't Use A Variable in a MySQL View

If you do, you'll get the 1351 error, because you can't use a variable in a view due to design. The bug/feature behavior is documented here.

Oracle has a rowid pseudo-column. In MySQL, you might have to go ugly:

SELECT id,       variety,       1 + (SELECT COUNT(*) FROM tbl WHERE < id) as num  FROM tbl

This query is off the top of my head and untested, so take it with a grain of salt. Also, it assumes that you want to number the rows according to some sort criteria (id in this case), rather than the arbitrary numbering shown in the question.