Как удалить все записи, кроме топ n относительно другого поля?

Question

Есть таблица такого формата:

CREATE TABLE rate
(
    --поля--
    direction        INTEGER NOT NULL,
    rate_main        DOUBLE PRECISION NOT NULL
);

Данные выглядят так:

direction | rate
1         |  5.9
1         |  1.23
1         |  4.2304
1         |  5.25
1         |  3.06
43        |  74.9
43        |  51.23
43        |  67.2304
43        |  32.25
43        |  1.06

С лимитом, например, в 3, в таблице должны остаться только эти записи:

 1         |  5.9
 1         |  5.25
 1         |  4.2304
 43        |  74.9
 43        |  67.2304
 43        |  51.23

а остальные удалиться.

Пробовал в этом направлении идти:

SELECT rate_main, direction -- DELETE
    FROM rate
    WHERE id IN (
        SELECT id
            FROM rate
--          WHERE direction = 4
            ORDER BY rate_main DESC
--          LIMIT 3
    )
    GROUP BY rate_main, direction
    ORDER BY rate_main DESC

но не получается.

Answer 1

Спасибо Aziz Umarov

Сделал так:

SELECT rate_main, direction, sub.rating_in_section
    FROM (SELECT rate_main
               , direction
               , row_number() OVER (PARTITION BY direction ORDER BY rate_main DESC ) AS rating_in_section
              FROM bc_rate
              ORDER BY direction, rating_in_section, rate_main) AS sub
    WHERE sub.rating_in_section <= 3
    ORDER BY direction, rate_main DESC;

Удаление:

DELETE
    FROM bc_rate
    WHERE id NOT IN (
        SELECT sub.id
            FROM (SELECT id
                       , rate_main
                       , direction
                       , row_number() OVER (PARTITION BY direction ORDER BY rate_main DESC ) AS rating_in_section
                      FROM bc_rate
                      ORDER BY direction, rating_in_section, rate_main) AS sub
            WHERE sub.rating_in_section <= top_in
    );

Answer 2

WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY direction ORDER BY rate_main DESC) rn
              FROM bc_rate )
SELECT *   -- заменить на список нужных полей
FROM cte 
WHERE rn < 4
-- ORDER BY список нужных полей

И соответственно удаление:

WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY direction ORDER BY rate_main DESC) rn
              FROM bc_rate )
DELETE
FROM cte 
WHERE rn > 3

БЛОГ НА HUSL

Как удалить все записи, кроме топ n относительно другого поля?

Ответы (2 шт):