CODEWITHSUNDEEP
sqlmysqlviewranking
Moak
Moak

Reputation: 12885

Row Rank in a MySQL View

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

Upvotes: 9

Views: 16990

Answers (2)

Marcelo Cantos
Marcelo Cantos

Reputation: 185862

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.

Upvotes: 4

OMG Ponies
OMG Ponies

Reputation: 332591

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.

Upvotes: 18

Related Questions