[SQL][leetcode] 177. Nth Highest Salary

Table: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id is the primary key column for this table.
Each row of this table contains information about the salary of an employee.

Write an SQL query to report the nth highest salary from the Employee table. If there is no nth highest salary, the query should report null.

The query result format is in the following example.

 

Example 1:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
n = 2
Output: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

Example 2:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
n = 2
Output: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| null                   |
+------------------------+

---

solution

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N = N-1;
    RETURN (
    	SELECT DISTINCT(salary) 
        FROM Employee 
        ORDER BY salary DESC
    	LIMIT 1 OFFSET N
  		); 
END

1. N번째 높은 salary를 찾아야 하므로 ORDER BY를 이용해 DESC 정렬

2. OFFSET 함수 -> 몇 번째 행부터 출력할 것인지 묻는 함수

 

※ 함수 생성 방법

CREATE FUNCTION '함수명' (파라미터)
RETURNS 반환할 데이터타입
BEGIN 수행할 쿼리
	RETURN 반환할 값
END