一、Nth Highest Salary Write a SQL query to get the nth highest salary from the Employee table. +---+---+ | Id | Salary | +---+---+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +---+---+ For example, given the above Employee table, the nth highest salary where n = ...
1#dropfunctiongetNthHighestSalary$$2CREATEFUNCTIONgetNthHighestSalary(NINT)RETURNSINT3BEGIN4DECLAREMINT;5SETM=N-1;6RETURN(7selectdistinctSalaryfromEmployeeorderbySalarydesclimit M,18);9END
Dense_Rank() OVER(ORDER BY Salary DESC) as RN FROM dbo.Employee ) SELECT EmpCode, EmpName, Salary FROM CTE WHERE RN = 2 GO Solution For SQL Server 2000, to get highest 2nd salary, we will use the query given below. SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP 2 ...
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGIN RETURN ( # Write your MySQL query statement below. );END 1. 参考1: CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGINDECLARE M INT;SET M=N-1; RETURN ( # Write your MySQL query statement below. SELECT DISTINCT Salary FRO...
Finding record of Nth highest salaried employee (where N, should equal to records or less then of the records), Here we are finding 1st , 2nd, 3rd and so on highest salaried employee’s name one by one using SQL Query. Here employee table, which has three fields eid(employee id), ...
SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M, 1 ); END 注意:这里不能直接在limit后面引用n-1,因为limit不识别计算公式,所以需要设置m = n-1 思路二: CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN RETURN ( ...
nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null. 题目解答: CREATEFUNCTION getNthHighestSalary(NINT)RETURNSINT BEGIN DECLARE MINT; SETM=N-1;
选第N大的元素思路已经很明朗了,由于 sql 内置的函数不存在选第N大元素的函数,所以我们只能另辟蹊径了,就根据Salary排序排序,然后用offset选排序后的第N个元素,最后用LIMIT限制选择一个元素,就是第N大的那个元素了。 本来我的解法是这样的: CREATEFUNCTIONgetNthHighestSalary(NINT)RETURNSINTBEGINDECLAREMINT;SETM...
DENSE_RANK() Without PARTITION BY Clause – The Nth Highest Record From the Entire Table Let’s apply DENSE_RANK() to the Employee Table to rank the employees based on their salary in the following example with aSELECT statement. SELECT[id],[name],[department],[salary],DENSE_RANK()OVER(...
retrieve nth salary thru optimized sql query Post ReplyBookmark TopicWatch Topic New Topic Forum: JDBC and Relational Databases You'll never get away with this you overconfident blob! The most you will ever get is this tiny ad: Gift giving made easy with the permaculture playing cards...