# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     name: python3
# ---

# + [markdown] id="view-in-github" colab_type="text"
# <a href="https://colab.research.google.com/github/Freya-LR/Leetcode/blob/main/177.Nth%20Highest%20Salary_sql.ipynb" target="_parent"><img src="https://colab.research.google.com/assets/colab-badge.svg" alt="Open In Colab"/></a>

# + [markdown] id="qSatyLD2fHXx"
# Input: 
# Employee table:
#
#  id | salary 
#
#  1  | 100    
#  2  | 200    
#  3  | 300    
#
# n = 2
#
# Output: 
#  getNthHighestSalary(2) 
#  
#  200                    
#

# + id="NjUB-dYtG_p9"
-- MS SQL

CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
BEGIN
    RETURN (
        /* Write your T-SQL query statement below. */
    SELECT DISTINCT Salary FROM (SELECT Salary,
                                 DENSE_RANK () OVER (ORDER BY Salary DESC) AS R
                                 FROM Employee )  TEM
        WHERE R=@N
    );
END

# + id="PeadJPOqertB"
-- MySQL
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
M=N-1;
RETURN ( 
    SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M,1
 );
END

StarcoderdataJupyterScriptsDedupFiltered