MYSQL RANK OVER and get the latest rank

Question

SELECT row_id, p_id, dt, 
     RANK() OVER (PARTITION BY p_id ORDER BY row_id ASC) AS rank
FROM tab
ORDER BY p_id, row_id ASC;

I want only the record with highest rank per p_id and i also want the rank. Is there a way to do so without putting the above query into a sub-query and selecting the MAX(rank)

Won't the highest rank in every window be 1? Anyway, you will most likely have to use a CTE or subquery. See this post for why: https://stackoverflow.com/questions/13997177/why-no-windowed-functions-in-where-clauses — mgrollins, Jul 05 '19 at 19:22

Gordon Linoff · Answer 1 · 2019-07-05T19:35:31.387

2

A subquery seems necessary in MySQL. It might be more efficient to use this, though:

SELECT t.*
FROM tab
ORDER BY row_id = (SELECT MAX(t2.row_id) FROM tab t2 WHERE t2.p_id = t.p_id);

edited Jul 05 '19 at 19:35

answered Jul 05 '19 at 19:04

Gordon Linoff

1,242,037
58
646
786

This gives me all the records with all ranks. I need only the record with highest rank and with the acquired rank also. – Vinod Nadar Jul 05 '19 at 19:13
@VinodNadar - What do you mean by "the acquired rank"? – Rick James Jul 05 '19 at 19:29
I meant i not only want the record with the highest rank which i can simply get by ordering the record in desc, i also want the rank it got – Vinod Nadar Jul 05 '19 at 19:32
@VinodNadar . . . This (and your query) return separate rows for each `pid`. – Gordon Linoff Jul 05 '19 at 19:36

score 1 · Answer 2 · answered Jul 05 '19 at 21:43

Test data:

CREATE TABLE tab ( p_id INT, row_id INT );

INSERT INTO tab VALUES
    (1, 1), (1, 2), (1, 3),
    (2, 1), (2, 2),
    (3, 1), (3, 2), (3, 3), (3, 4);

Query:

WITH cte AS (
 SELECT row_id, p_id,
  RANK() OVER (PARTITION BY p_id ORDER BY row_id ASC) AS `rank`,
  ROW_NUMBER() OVER (PARTITION BY p_id ORDER BY row_id DESC) AS `rownum`
 FROM tab
)
SELECT * FROM cte WHERE rownum = 1

Output:

+--------+------+------+--------+
| row_id | p_id | rank | rownum |
+--------+------+------+--------+
|      3 |    1 |    3 |      1 |
|      2 |    2 |    2 |      1 |
|      4 |    3 |    4 |      1 |
+--------+------+------+--------+

score 0 · Answer 3 · answered Jul 06 '19 at 06:35

As it seems i need to use a subquery anyways so i ended up as below

Sample Data

    row_id  p_id    dt
    1       1       2018-07-01
    2       3       2019-06-01
    3       2       2017-05-13
    4       1       2018-09-05
    5       2       2017-09-25
    6       3       2019-06-02
    7       2       2019-01-01
    8       1       2019-01-06
    9       1       2019-05-03

Query

WITH cte AS (
    SELECT row_id, p_id, dt,
        RANK() OVER (PARTITION BY p_id ORDER BY row_id ASC) rank1,
        RANK() OVER (PARTITION BY p_id ORDER BY row_id DESC) rank2
    FROM tab
)
SELECT * FROM cte WHERE rank2 = 1;

Output

row_id   p_id  dt,          rank1   rank2
6        3     2019-06-02   2       1
7        2     2019-01-01   3       1
9        1     2019-05-03   4       1

MYSQL RANK OVER and get the latest rank

3 Answers3