SQLite：每個欄位只保留 n 行

我有一張桌子

+-----------------------------+---------------+-----------+
|         Painter             | Painting      | Rate      |
+-----------------------------+---------------+-----------+
|  Zinaida Serebriakova       | Window        | 5         |
|  Zinaida Serebriakova       | Florence      | 1         |
|  Zinaida Serebriakova       | Nude          | 8         |
|  Zinaida Serebriakova       | Bath          | 4         |
|  Thomas And William Daniell | Turbine Makers| 2         |
|  Thomas And William Daniell | George Iain   | 7         |
|  Thomas And William Daniell | Flax Pullers  | 3         |
|  Robert Motherwell          | Galleons      | 1         |
|  Robert Motherwell          | Ulysses       | 2         |
+-----------------------------+---------------+-----------+

我需要為每位畫家獲得 2 幅畫作，獲得率最高。

+-----------------------------+---------------+-----------+
|         Painter             | Painting      | Rate      |
+-----------------------------+---------------+-----------+
|  Zinaida Serebriakova       | Nude          | 8         |
|  Zinaida Serebriakova       | Window        | 5         |
|  Thomas And William Daniell | George Iain   | 7         |
|  Thomas And William Daniell | Flax Pullers  | 3         |
|  Robert Motherwell          | Ulysses       | 2         |
|  Robert Motherwell          | Galleons      | 1         |
+-----------------------------+---------------+-----------+

並刪除其他所有內容！

WITH 
cte AS ( SELECT author, 
               name, 
               rating, 
               ROW_NUMBER() OVER ( PARTITION BY author 
                                   ORDER BY rating DESC ) rn
        FROM ratings )
DELETE
FROM ratings
WHERE EXISTS ( SELECT 1
              FROM cte
              WHERE ratings.author = cte.author
                AND ratings.name = cte.name
                AND cte.rn > 2 );

小提琴

另一個使用row_number()視窗函式的版本（因此需要 sqlite 3.25 或更高版本）：

DELETE FROM ratings
WHERE rowid IN (SELECT rowid
               FROM (SELECT rowid
                          , row_number() OVER (PARTITION BY painter ORDER BY rate DESC) AS rn
                     FROM ratings)
               WHERE rn > 2);

引用自：https://dba.stackexchange.com/questions/242080