How To Find A Function From A Table

Finding Nth highest salary in a tabular array is the virtually common question asked in interviews. Here is a manner to practice this task using dense_rank() function.

Consider the following table:
Employee

ename	sal
A	23000
B	31000
C	24500
D	35000
Due east	28500
F	31500
G	39800
H	51000
I	39800

Query :

select * from( select ename, sal, dense_rank()  over(guild by sal desc)r from Employee)  where r=&n;  To find to the 2d highest sal gear up n = 2 To discover tertiary highest sal set n = 3 and then on.

Output :

DENSE_RANK :

DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers kickoff with 1.
This function accepts arguments every bit whatever numeric data type and returns NUMBER.
As an analytic function, DENSE_RANK computes the rank of each row returned from a query with respect to the other rows, based on the values of the value_exprs in the order_by_clause.
In the to a higher place query the rank is returned based on sal of the employee tabular array. In instance of tie, it assigns equal rank to all the rows.

Alternate Solution :
—————————————————————————————————————————————————————————————————————–

CREATE Tabular array `Employee` (  `ENAME` varchar(225) COLLATE utf8_unicode_ci NOT Aught,  `SAL` bigint(xx) unsigned NOT NULL,  Chief Key (`ENAME`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

————————————————————————————————————————————————————-

6th highest mysql> select * from ((select * from Employee         ORDER By `sal` DESC limit 6 ) AS T)         Guild BY T.`sal` ASC limit ane; Alternate use of Limit: select * from Employee Society By `sal` ASC limit 5,one; // will render 6th highest    +-------+-----+ | ENAME | SAL | +-------+-----+ | B     | 300 | +-------+-----+ 1 row in gear up (0.00 sec)

——————————————————————————————————————————————————–

mysql> select * from Employee; +-------+-----+ | ENAME | SAL | +-------+-----+ | A     | 100 | | B     | 300 | | C     | 200 | | D     | 500 | | F     | 400 | | G     | 600 | | H     | 700 | | I     | 800 | +-------+-----+ eight rows in set (0.00 sec)

Thanks to Vijay for suggesting this alternate solution.

Alternating Solution –
Suppose the task is to discover the employee with the Nth highest salary from the in a higher place tabular array. We can exercise this as follows:

Notice the employees with tiptop N distinct salaries.
Discover the lowest salary amidst the salaries fetched by the in a higher place query, this volition give us the Nth highest salary.
Observe the details of the employee whose salary is the lowest salary fetched by the above query.

Query:

SELECT * FROM Employee WHERE sal =           (             SELECT MIN(sal) FROM Employee              WHERE  sal IN (                                  SELECT Singled-out TOP          N          sal FROM Employee                                           Gild BY sal DESC                              )         )

The above query will fetch the details of the employee with the Nth highest salary. Let u.s.a. encounter how:

Consider N = 4.
Starting with the most inner query, the query: "SELECT Singled-out Tiptop 4 sal FROM Employee Club Past sal DESC" will produce the below result:

51000 39800 35000 31500

The next outer query is: "SELECT MIN(sal) FROM Employee WHERE sal IN ( Result_Set_of_Previous_Query )". This volition return the below effect:

You tin can run into that the above returned result is the required 4th highest salary.
Adjacent is the most outer query, which is: "SELECT * FROM Employee WHERE sal = Result_of_Previous_Query". This query will render the details of employees with fourth highest salary.

________________________ ename             sal ________________________   F        |     31500            | ________________________

Some other Solution –
Here North = nth Highest Salary eg. 3rd Highest salary : Northward=3 .

SELECT ename,sal from Employee e1 where          N-one = (SELECT COUNT(Distinct sal)from Employee e2 where e2.sal > e1.sal)

Solution using Limit :

                              Select Salary from table_name order by Salary DESC limit n-1,one;

Here nosotros are ordering our salary in descending order so nosotros will become highest salary first and then afterward lower salaries.

Limit clause has two components, First component is to skip number of rows from peak and second component is display number of rows we want.

let us see with an example :

To find 4th Highest bacon query will be :

          Select Bacon from table_name order by Salary DESC limit 3,1;

Hither we are skipping three rows from Height and returning just ane row after skipping .

You can also detect names of employees having Nth Highest Salary

Select Emp_name from table_name where Salary =( Select Salary from table_name social club by Salary DESC limit northward-ane,1);

At that place can be another question like discover Nth Everyman Bacon . In order to that , just reverse order using ASC ( if you don't specify by default column will be ordered in ascending lodge).

          Select Salary from table_name gild by Salary limit n-1,1;

&t=176s

This commodity is contributed by Rishav Shandilya. If y'all similar GeeksforGeeks and would like to contribute, y'all can also write an commodity using write.geeksforgeeks.org or mail your article to review-team@geeksforgeeks.org. See your commodity appearing on the GeeksforGeeks chief page and aid other Geeks.