Row Rank in a MySQL View

Question

I need to create a view that automatically adds virtual row number in the result. the graph here is totally random all that I want to achieve is the last column to be created dynamically.

> +--------+------------+-----+
> | id     | variety    | num |
> +--------+------------+-----+
> | 234    | fuji       |   1 |
> | 4356   | gala       |   2 |
> | 343245 | limbertwig |   3 |
> | 224    | bing       |   4 |
> | 4545   | chelan     |   5 |
> | 3455   | navel      |   6 |
> | 4534345| valencia   |   7 |
> | 3451   | bartlett   |   8 |
> | 3452   | bradford   |   9 |
> +--------+------------+-----+

Query:

SELECT id, 
       variety, 
       SOMEFUNCTIONTHATWOULDGENERATETHIS() AS num 
  FROM mytable

Possible duplicate of [ROW\_NUMBER() in MySQL](http://stackoverflow.com/questions/1895110/row-number-in-mysql) — Ciro Santilli OurBigBook.com, May 25 '16 at 14:10

OMG Ponies · Accepted Answer · 2009-12-27T01:57:16.913

Use:

SELECT t.id,
       t.variety,
       (SELECT COUNT(*) FROM TABLE WHERE id < t.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.id,
       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.

good solution, however i can't get it into my view: #1351 - View's SELECT contains a variable or parameter :/ — Moak, Dec 27 '09 at 01:36
You can probably define the variable before you run the query, so there's no need for the subquery. I'm not sure if it's a connection/cursor specific var, or if it's global, so you'd have to figure that out first. — Tor Valamo, Dec 27 '09 at 02:03
@Tor: If it were for a stored procedure, but the OP asks for this in a View specifically. — OMG Ponies, Dec 27 '09 at 02:14

score 4 · Answer 2 · edited Nov 27 '11 at 18:20

4

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

SELECT id,
       variety,
       1 + (SELECT COUNT(*) FROM tbl WHERE t.id < 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.

edited Nov 27 '11 at 18:20

Adam Wenger

17,100
6
52
63

answered Dec 27 '09 at 01:02

Marcelo Cantos

181,030
38
327
365

I think you mean `(SELECT COUNT(*) FROM tbl WHERE t.id < id) + 1 AS num` - COUNT doesn't take a SELECT statement as a parameter: http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_count – OMG Ponies Dec 27 '09 at 01:18
+1: Tested the corrected version, and the output matches the expected results. – OMG Ponies Dec 27 '09 at 02:03

Row Rank in a MySQL View

2 Answers2

MySQL Ranking, or Lack Thereof

Can't Use A Variable in a MySQL View

Linked