SELECT
*
FROM
Employee E1
WHERE
(N - 1) = (SELECT
COUNT(DISTINCT (E2.salary))
FROM
Employee E2
WHERE
E2.salary > E1.salary)
SQL to find nth highest salary
Carvia Tech | July 23, 2020 | 1 min read | 532 views
Each row of Employee needs to be compared to every other row based on salary to figure out the nth highest record, thus the Time Complexity of this operation would be quite high Big O (n2)
SQL nth highest salary
For example, when we want to find out an employee with 2nd higest salary, SQL will be:
SQL - Employee with 2nd highest salary
SELECT
*
FROM
Employee E1
WHERE
(2 - 1) = (SELECT
COUNT(DISTINCT (E2.salary))
FROM
Employee E2
WHERE
E2.salary > E1.salary)
Top articles in this category:
- SQL - Write a query to find customers who have no orders yet
- Morgan Stanley Java Interview Questions
- Citibank Java developer interview questions
- Cracking core java interviews - question bank
- Goldman Sachs Java Interview Questions
- Sapient Global Market Java Interview Questions and Coding Exercise
- BlackRock Java Interview Questions
Find more on this topic:
Subscribe to Interview Questions
Recommended books for interview preparation:
Book you may be interested in..
Book you may be interested in..
Similar Posts
- Finastra Investment Banking Interview Questions
- Merge two sorted array into a single sorted array
- Spring Boot with GMAIL SMTP
- Mandrill emails in Spring Boot Java
- Hibernate & Spring Data JPA interview questions
- Generating cryptographically strong key/secret in Java
- Reverse the bits of a number and check if the number is palindrome or not
- MD5 and SHA256 in Java Kotlin and Android
- There is no PasswordEncoder mapped for the id
- Inter-thread communication in Java