postgresql - PostgreSql 找到第 n 个数字

Question

编写一个在列中找到第三大整数的聚合？这就是我正在做的事情：

create function third_step(bigint, integer) returns bigint AS
$$

SELECT distinct
 * from count_lab order by ab asc limit 1 offset 2 ;


$$ language sql; --can we use plpgsql somehow?

create aggregate third_agg(integer)
(stype=bigint,sfunc=third_step,initcond=0); 
SELECT distinct
 * from count_lab order by ab asc limit 1 offset 2  ;

标签： postgresql

Answer 1

它应该是：

SELECT DISTINCT ab 
    FROM count_lab 
    WHERE ab IS NOT NULL 
    ORDER BY ab DESC 
    LIMIT 1 OFFSET 2;