Row Rank in a MySQL View

by

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.

More Related Contents:

  1. Using LIMIT within GROUP BY to get N results per group?
  2. Join vs. sub-query
  3. How do I update if exists, insert if not (AKA “upsert” or “merge”) in MySQL?
  4. Natural Sort in MySQL
  5. MySQL get row position in ORDER BY
  6. Subqueries vs joins
  7. MySQL Results as comma separated list
  8. MySQL unknown column in ON clause
  9. MySQL not using indexes with WHERE IN clause?
  10. SQL: Find the max record per group [duplicate]
  11. Group by month and year in MySQL
  12. calculate a sum of type time using sql
  13. Why is using ‘*’ to build a view bad?
  14. MySQL: Transactions vs Locking Tables
  15. Remove duplicates using only a MySQL query?
  16. Group by date only on a Datetime column
  17. Finding the next available id in MySQL
  18. How exactly does using OR in a MySQL statement differ with/without parentheses?
  19. MySQL: How to copy rows, but change a few fields?
  20. How to renumber primary index
  21. Query that ignore the spaces
  22. Is it possible to have a default parameter for a mysql stored procedure?
  23. mysql automatically store record creation timestamp
  24. update columns values with column of another table based on condition [duplicate]
  25. How do I list all the columns in a table?
  26. Two single-column indexes vs one two-column index in MySQL?
  27. VARCHAR as foreign key/primary key in database good or bad?
  28. How do I create a table alias in MySQL
  29. Select Rows with Maximum Column Value group by Another Column
  30. Why isn’t row level security enabled for Postgres views?

Leave a Comment