# 177. 第N高的薪水
# https://leetcode-cn.com/problems/nth-highest-salary/

# SQL架构
Create table If Not Exists Employee (Id int, Salary int);
Truncate table Employee;
insert into Employee (id, salary) values ('1', '100');
insert into Employee (id, salary) values ('2', '200');
insert into Employee (id, salary) values ('3', '300');

DELIMITER $$;
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT

# SET GLOBAL log_bin_trust_function_creators = 1;
READS SQL DATA
DETERMINISTIC

BEGIN
  RETURN (
      # Write your MySQL query statement below.
    select
        distinct e1.salary
    from
        Employee e1
    where
        (
            select
                count(distinct e2.salary)
            from
                Employee e2
            where
                e2.salary > e1.salary
        ) = N-1
  );
END$$
DELIMITER ;$$

# clean-up
drop table Employee;
drop function getNthHighestSalary;