Leetcode:nth highest Salary-nth high wages

Source: Internet
Author: User
Tags mysql command line

1. Title

Nth highest Salary (nth high salary)

2. Address of the topic

https://leetcode.com/problems/nth-highest-salary/

3. Topic content

Compared with this topic, the previous topic "Second highest Salary" is the special case in the n=2. The structure of the employee table is exactly the same as for the two-Problem purpose table:

+----+--------+| Id | Salary |+----+--------+| 1 | 100 | | 2 | 200 | | 3 | |+----+--------+

4. Initializing Database Scripts

Create a database named Leetcode in the MySQL database and execute the following script with the source command in the MySQL command line:

--a databaseuse leetcode named Leetcode must be established before executing the script; DROP TABLE IF EXISTS Employee; CREATE TABLE Employee (Id int not NULL PRIMARY KEY, Salary int); INSERT into employee (ID, Salary) VALUES (1, +), insert into employee (ID, Salary) VALUES (2, $); INSERT into employee (  ID, Salary) VALUES (3, $); INSERT into employee (ID, Salary) VALUES (4, +); INSERT into employee (ID, Salary) VALUES (5, INSERT into Employee (Id, Salary) VALUES (6, 400);

5. Solving SQL

The solution to the previous question can refer to this article blog:http://my.oschina.net/tsybius2014/blog/493086

This problem is similar to the solution of the previous question, that is, the source data is sorted by salary column from large to small, and then the nth row of the sorted result set is taken directly, and the value of the row data salary column is the first n salary. The difference is that the subject needs to be implemented in the form of a function, noting that the limit clause is not directly followed by N-1.

CREATE FUNCTION getnthhighestsalary (n INT) RETURNS intbegin SET n = N-1; RETURN (Select Case if COUNT (Salary) >= 1 then (select DISTINCT Salary from Employee ORDER B Y Salary DESC LIMIT N, 1) ELSE NULL END as nthsalary from Employee); END

END

Leetcode:nth highest Salary-nth high wages

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.