MYSQL排名超过并获得最新排名

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;

我只想与每个p_id的最高排名的记录，我也想排名。有没有一种方法可以做到这一点，而不是将上面的查询放入子查询中并选择最大(排名)

Answer 1

在MySQL中，子查询似乎是必需的。不过，使用下面的代码可能更有效：

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

Answer 2

测试数据：

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);

查询：

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

输出：

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

Answer 3

看起来我无论如何都需要使用子查询，所以我的结果如下所示

样本数据

    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

查询

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;

输出

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