Row Rank in a MySQL View

Row Rank in a MySQL View

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.

How to get a row rank?

The subquery approach that you have seen recommended will scale quadratically. http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/ shows a much more efficient approach with user variables. Here is an untested adaptation to your problem:

@points := -1; // Should be an impossible value.
@num := 0;

SELECT id
  , points
  , @num := if(@points = points, @num, @num + 1) as point_rank
  , @points := points as dummy
FROM `users`
ORDER BY points desc, id asc;

Create View with rank column in limited amount of data

First you need to create a first VIEW that sums the spent time for every user on the same week:

CREATE VIEW total_spent_time AS
SELECT userid,
       serverid,
       sum(time) AS total_time,
       yearweek(day, 3) as week
FROM spenttime
GROUP BY userid, serverid, week;

then you can create your view as this:

CREATE VIEW spenttime_week AS
 SELECT
  s1.userid,
  s1.serverid,
  s1.total_time,
  s1.week,
  count(s2.total_time)+1 AS rank
FROM
  total_spent_time s1 LEFT JOIN total_spent_time s2
  ON s1.serverid=s2.serverid
     AND s1.userid!=s2.userid
     AND s1.week = s2.week
     AND s1.total_time<=s2.total_time
GROUP BY  
  s1.userid,
  s1.serverid,
  s1.total_time,
  s1.week
ORDER BY
  s1.week, s1.serverid, s1.userid

Please see a fiddle here.

MySQL: use group's earliest date to determine each row's rank

I renamed your table to test1 and the group column to groupp because group is a reserved word.

The following query will give you the desired result

SELECT t1.*
FROM (SELECT id, groupp, min(date), @rownum := @rownum + 1 as rank
      FROM test1, (SELECT @rownum := 0) r
      GROUP BY groupp
      ORDER BY date ASC) qry
INNER JOIN test1 t1 on t1.groupp = qry.groupp
ORDER BY qry.rank, t1.date asc;

The query marked with qry will get you the record with minimum date for each groupp.
The query t1 will get you all the records from the table.
When you join these two on column groupp you will basically get all the records from the table with the appropriate rank based on the result from the first query.
Then you just order by rank and date.

I tested the query and it returns the desired result with sql fiddle

http://sqlfiddle.com/#!9/90dee/1

how to get position rank of specific row using just mysql query?

SELECT users1.id, (users1.points+users1.extra_points) AS total, COUNT(*)+1 AS rank
FROM users users1
INNER JOIN users users2 ON users1.points+users1.extra_points < users2.points+users2.extra_points
WHERE users1.id = $id

Determine Rank based on Multiple Columns in MySQL

In a Derived Table (subquery inside the FROM clause), we order our data such that all the rows having same user_id values come together, with further sorting between them based on game_detail in Descending order.

Now, we use this result-set and use conditional CASE..WHEN expressions to evaluate the row numbering. It will be like a Looping technique (which we use in application code, eg: PHP). We would store the previous row values in the User-defined variables, and then check the current row's value(s) against the previous row. Eventually, we will assign row number accordingly.

Edit: Based on MySQL docs and @Gordon Linoff's observation:

The order of evaluation for expressions involving user variables is
undefined. For example, there is no guarantee that SELECT @a, @a:=@a+1
evaluates @a first and then performs the assignment.

We will need to evaluate row number and assign the user_id value to @u variable within the same expression.

SET @r := 0, @u := 0; 
SELECT
  @r := CASE WHEN @u = dt.user_id 
                  THEN @r + 1
             WHEN @u := dt.user_id /* Notice := instead of = */
                  THEN 1 
        END AS user_game_rank, 
  dt.user_id, 
  dt.game_detail, 
  dt.game_id 

FROM 
( SELECT user_id, game_id, game_detail
  FROM game_logs 
  ORDER BY user_id, game_detail DESC 
) AS dt 

Result

| user_game_rank | user_id | game_detail | game_id |
| -------------- | ------- | ----------- | ------- |
| 1              | 6       | 260         | 11      |
| 2              | 6       | 100         | 10      |
| 1              | 7       | 1200        | 10      |
| 2              | 7       | 500         | 11      |
| 3              | 7       | 260         | 12      |
| 4              | 7       | 50          | 13      |

View on DB Fiddle

---

An interesting note from MySQL Docs, which I discovered recently:

Previous releases of MySQL made it possible to assign a value to a
user variable in statements other than SET. This functionality is
supported in MySQL 8.0 for backward compatibility but is subject to
removal in a future release of MySQL.

Also, thanks to a fellow SO member, came across this blog by MySQL Team: https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/

General observation is that using ORDER BY with evaluation of the user variables in the same query block, does not ensure that the values will be correct always. As, MySQL optimizer may come into place and change our presumed order of evaluation.

Best approach to this problem would be to upgrade to MySQL 8+ and utilize the Row_Number() functionality:

Schema (MySQL v8.0)

SELECT user_id, 
       game_id, 
       game_detail, 
       ROW_NUMBER() OVER (PARTITION BY user_id 
                          ORDER BY game_detail DESC) AS user_game_rank 
FROM game_logs 
ORDER BY user_id, user_game_rank;

Result

| user_id | game_id | game_detail | user_game_rank |
| ------- | ------- | ----------- | -------------- |
| 6       | 11      | 260         | 1              |
| 6       | 10      | 100         | 2              |
| 7       | 10      | 1200        | 1              |
| 7       | 11      | 500         | 2              |
| 7       | 12      | 260         | 3              |
| 7       | 13      | 50          | 4              |

View on DB Fiddle

---

Related Topics