How to add rank for each row in mysql view

Question

I have this view my mysql database

DROP VIEW IF EXISTS time_all;
CREATE VIEW `time_all` AS
SELECT `scans`.`datetime` FROM `time_clock`.`scans`
ORDER BY `scans`.`datetime` ASC;

I want to add unique rank value for each row in this view, can anyone tell me how to do that?

Possible duplicate of [MySQL - Get row number on select](https://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select) — Jorge Campos, Jul 28 '17 at 23:09
As others have suggested, first figure out which RDBMS you're using. — Strawberry, Jul 29 '17 at 00:38

score 1 · Answer 1 · edited Jul 29 '17 at 06:59

I'm not quite sure what you mean by "unique rank". Something like this probably does what you want:

CREATE VIEW `time_all` AS
    SELECT s.`datetime,
           (SELECT COUNT(*) FROM `time_clock`.`scans` s2 WHERE s2.datetime <= s.datetime) as rank
    FROM `time_clock`.`scans` s
    ORDER BY `scans`.`datetime` ASC;

MySQL supports neither window functions nor CTEs. Nor does it support variables in views. A correlated subquery is one of the few ways of doing this.

score 0 · Answer 2 · answered Jul 28 '17 at 23:52

Solved with this method

  delimiter $$
    DROP FUNCTION IF EXISTS `getRank`$$
    CREATE FUNCTION `getRank`() RETURNS int(11)
        DETERMINISTIC
    begin
    return if(@rankId, @rankId:=@rankId+1, @rankId:=1);
    end$$

Now if I want to use the function I created, just use it like this

CREATE VIEW time_all AS
SELECT getRank() AS 'rank', `scans`.`datetime` FROM `time_clock.scans`
ORDER BY scans.datetime ASC;

How to add rank for each row in mysql view

2 Answers2