Solve this Sql Query -Part 2

Manish Goyal · 2011-06-01T14:55:10+00:00

Continued to my previous query #-Link-Snipped-# Let us make this query a little bit tricky Say i want to develop a single query, through which i can extract data of all the employee having 5th highest salary . Remember it should be single query ,that next time if i want salary of employee having maximum salary among all,it should display it too Note :-you have to mention which variables in query are required to be modified dynamically every time

Solve this Sql Query -Part 2

Manish Goyal
@manish-r2Hoep

Updated: Oct 26, 2024

Views: 1.2K

Continued to my previous query #-Link-Snipped-#

Let us make this query a little bit tricky

Say i want to develop a single query, through which i can extract data of all the employee having 5th highest salary .

Remember it should be single query ,that next time if i want salary of employee having maximum salary among all,it should display it too

Note :-you have to mention which variables in query are required to be modified dynamically every time

0

Replies

Howdy guest!

Dear guest, you must be logged-in to participate on CrazyEngineers. We would love to have you as a member of our community. Consider creating an account or login.

Replies

ISHAN TOPRE

Member • Jun 1, 2011

I know a little. Is it

Select * from employee where Salary< salary1, salary 2, salary 3, salary4

?? I do not know much but we can define salary 1,2,3,4. In this way we can get the highest salaried employee's data too.

Are you sure? This action cannot be undone.
Cancel
Sahithi Pallavi

Member • Jun 2, 2011

@ishu : that's not the right answer I guess because if we want the details of the employee having 20th highest salary, then its difficult to mention all the numbers..

I don't know the answer, I am trying now!

Are you sure? This action cannot be undone.
Cancel
synergynext

Member • Jun 3, 2011
```
SELECT * FROM table_name a
WHERE 5 = (SELECT COUNT(rowid)
FROM table_name b
WHERE a.rowid >= b.rowid);
 
```
a and b are alias of same table. you can replace 5 by any number you want. This type of questions is very popular for freshers' interviews 😀
Are you sure? This action cannot be undone.
Cancel
synergynext

Member • Jun 3, 2011
Correction:
didnt read highest salary earlier:

correct solution is as follows:
```
SELECT MIN(salary)
FROM (SELECT salary FROM employees ORDER BY salary DESC)
WHERE ROWNUM <= 5;
```
Are you sure? This action cannot be undone.
Cancel
Manish Goyal

Member • Jun 3, 2011

That's nice answer,
I want single query ,you can modify it dynamically in order to get right answer.

Are you sure? This action cannot be undone.
Cancel
synergynext

Member • Jun 3, 2011
```
SELECT rownum, salary FROM employee 
GROUP BY rownum, salary HAVING rownum = 5
 
```
enjoy
Are you sure? This action cannot be undone.
Cancel
Manish Goyal

Member • Jun 3, 2011

synergynext
SELECT rownum, salary FROM employee
GROUP BY rownum, salary HAVING rownum = 5

enjoy
Ok say i have deleted employee data whose id is 5 ,then will this query work?

Are you sure? This action cannot be undone.
Cancel
synergynext

Member • Jun 3, 2011

I hope you are aware of "What Rownum is"!

Are you sure? This action cannot be undone.
Cancel
Manish Goyal

Member • Jun 3, 2011

synergynext
I hope you are aware of "What Rownum is"!
hmm actually i am aware of Mysql only ,but this query is not working there,

Are you sure? This action cannot be undone.
Cancel
synergynext

Member • Jun 3, 2011
hmmm,
If you have mysql installed, can you try this one and let me know if it works
```
 
SELECT * FROM employee ORDER BY salary DESC LIMIT 5,4
```
Are you sure? This action cannot be undone.
Cancel
synergynext

Member • Jun 3, 2011

or LIMIT 4, 5
whichever works haha

Are you sure? This action cannot be undone.
Cancel
synergynext

Member • Jun 3, 2011

goyal420
Ok say i have deleted employee data whose id is 5 ,then will this query work?
rownum is a pseudo column in Oracle sql plus. Suppose if you run the query that uses rownum, and delete that record. Now when you retry the query, you will get new output which will be 5th highest salary at that time.

Are you sure? This action cannot be undone.
Cancel
sookie

Member • Jun 7, 2011
```
 select * from (
select name,salary,dense_rank() over(order by salary desc) dr
from employee
) where dr = :rankOrder
```
Enter rankOrder as 5 to get the output .
Are you sure? This action cannot be undone.
Cancel
synergynext

Member • Jun 8, 2011
His initial requirement was to have a single query, not a nested one.
In my opinion this one is the answer:
```
SELECT rownum, salary FROM employee 
GROUP BY rownum, salary HAVING rownum = 5
```
Later he asked it for MySQL, so this would run fine on MySQL.
```
SELECT * FROM employee ORDER BY salary DESC LIMIT 4,5 
```
Both are single queries.
Are you sure? This action cannot be undone.
Cancel
sookie

Member • Jun 9, 2011

@synergynext "rownum" fails when you have two employees having same salary say '35000' as 5th highest salary. It will give you one record always in spite of having many two.

Are you sure? This action cannot be undone.
Cancel
PraveenKumar Purushothaman

Member • Jun 28, 2011
I guess the best SQL Query would be:
```
SELECT * FROM `emp` GROUP BY `salary` DESC LIMIT 4,5
```
Are you sure? This action cannot be undone.
Cancel