Returning the nth largest value in postgresql-CodePudding

I want to return the nth largest salary in the table using for-loops, and I am still having trouble with the syntax. Please help.

create function get_nth_max(n integer)
returns real
as
$$
declare
nth_max real = max(salary) from company;
pay real;
begin
for pay in select salary from company 
    order by salary = desc limit n 
    loop
        if pay.salary < nth_max then
        nth_max = pay.salary and
end loop;
return nth_max
end;
$$
language plpgsql;

Error message:

ERROR:  syntax error at or near "desc"
LINE 10:  order by salary = desc limit n loop
                            ^
SQL state: 42601
Character: 182

CodePudding user response：

You don't need a UDF for this, just use DENSE_RANK:

WITH cte AS (
    SELECT *, DENSE_RANK() OVER (ORDER BY salary DESC) drnk
    FROM company
)

SELECT salary
FROM cte
WHERE drnk = <value of n here>

CodePudding user response：

So I have missing semicolons, did not end my if-statement, and a 'dangling' AND. The following code is the working version:

create or replace function get_nth_max(n integer)
returns real
as
$$
declare
nth_max real = max(salary) from company;
pay record;
begin
for pay in select salary from company 
    order by salary desc limit n 
    loop
        if pay.salary < nth_max then
        nth_max = pay.salary;
        end if;
    end loop;
return nth_max;
end;
$$
language plpgsql;