Home
Threads
Solve this Sql Query

Solve this Sql Query

Manish Goyal

@manish-r2Hoep • Oct 26, 2024

Oct 26, 2024

2.1K

Consider a database which stores employee data having following fields

Name
Destination
Department
Salary

Now you have to write a sql query such that it will display the data of employee whose salary is second highest among all

Can you solve this?😛

0 47

Replies

Welcome, guest

Join CrazyEngineers to reply, ask questions, and participate in conversations.

CrazyEngineers powered by Jatra Community Platform

Leo

@leo-ZJQlmh • Sep 2, 2010

Hey buddy is that your home work.

0
Manish Goyal

@manish-r2Hoep • Sep 2, 2010

no buddy it's not my homework

I have just given a problem.Actually these problems generally comes when you work on some project

if no one will answer this then i will give answer

0
bohar

@bohar-wQr9Oe • Sep 2, 2010

select max(salary) from employee where <(select max(salary) from employee)

0
Morningdot Hablu

@morningdot-6Xuj4M • Sep 2, 2010

SELECT MAX(salary) FROM employee WHERE (salary!=MAX(salary));

am i right goyal...??

0
Manish Goyal

@manish-r2Hoep • Sep 2, 2010

@mohit no it is not correct

@bohar:-You are right
It should be like this .
select max(salary) from employee where salary < (select max(salary) from employee);

0
Morningdot Hablu

@morningdot-6Xuj4M • Sep 2, 2010

@goyal but i think it will display all the salary which is less then max.

and where i am incorrect...??

0
Manish Goyal

@manish-r2Hoep • Sep 3, 2010

When i first saw your query

it seems like it will will display data of all the employees whose salary is not equal to maximum salary but i want data of only one employee with second highest salary

but when i execute your query then it shows error

"Invalid use of Group function".

0
Morningdot Hablu

@morningdot-6Xuj4M • Sep 3, 2010

select max(salary) from employee where salary < (select max(salary) from employee);
hey you question is to display 2nd highest salary then i think above query will also display all the salary less than max salary.

0
Manish Goyal

@manish-r2Hoep • Sep 3, 2010

no bro ,i want data of only one employee whose salary is second highest among all

Really it's not working ,you can check it out

0
nandini.arun

@nandiniarun-82mU7b • Feb 27, 2011

Hi the below query should work in retrieving the second highest salary from the table.

Select max(salary) from emp where salary not in(select max(salary) from emp);

0
slashfear

@slashfear-tSWzpz • Feb 28, 2011

goyal420@mohit no it is not correct

@bohar:-You are right
It should be like this .
select max(salary) from employee where salary < (select max(salary) from employee);

Hi Goyal,

you want to select the data of employee who is having the second highest salary but the query bohar has given will just give you the second max salary and not his data!! so you have to modify the query a little bit like this to get the correct output:

select * from emp where salary = (select max(salary) from emp where salary < (select max(salary) from emp));

@mohit the reason why your query is wrong is because you cannot use aggregate function in where condition, you have to use sub query and the place when you have to use in or not in clause is when the sub query returns you with more than 1 result set. If you have any doubt you can ask ...... 😀

-Arvind

0

PraveenKumar Purushothaman

@praveenkumar-66Ze92 • Mar 2, 2011

Hey dude... I guess I got the answer with me... I will provide the query for Oracle and MySQL. I have tested them too... Consider this:

[B]DESC `employee`;[/B]
    EmpID   INT
    Salary  FLOAT

[B]SELECT * FROM `employee`;[/B]
    EmpID   Salary
        1    50000
        2     7000
        3    35000
        4    15000
        5    20000
        6     5000
        7    12000
        8     6000
        9     8500
       10    10000

[B]SELECT * FROM `employee` SORT BY `Salary` DESC[/B]
    EmpID   Salary
        1    50000
        3    35000
        5    20000
        4    15000
        7    12000
       10    10000
        9     8500
        2     7000
        8     6000
        6     5000

Till this, it is common in both MySQL and Oracle.
For MySQL, use this query.

[B]SELECT * FROM `employee` WHERE `Salary` < max(`Salary`) LIMIT 1[/B];
        3    35000

Oracle has a column for all the results, called ROWNUM. You can make use of it here.

[B]SELECT * FROM `employee` SORT BY `Salary` DESC WHERE ROWNUM = 2;[/B]
        3    35000

I guess this could be useful!

sinish

@sinish-zu0O00 • May 27, 2011

goyal420Consider a database which stores employee data having following fields

Name
Destination
Department
Salary

Now you have to write a sql query such that it will display the data of employee whose salary is second highest among all

Can you solve this?😛
select max(salary)
from dabase
where salary <
(select max(salary) from database);

0
PraveenKumar Purushothaman

@praveenkumar-66Ze92 • May 27, 2011

sinishselect max(salary)
from dabase
where salary <
(select max(salary) from database);
This doesn't work... No salary is greater than the MAX salary... So, a small correction... 😀
make it salary = MAX(salary) and LIMIT = 1 😁

0
sookie

@sookie-T06sFW • May 29, 2011
I just don't understand why this thread is still under discussion.

@Praveen : Correct me if wrong, but your query will fail in the case when there can be more than 1 employees who have second highest salary and ideally the original question should be "Finding out the list of employees[Not only ONE employee..it will be partiality then with that ONE employee 😉 ] who have second highest salary.

So query given by "slashfear" will work in any kind of data. [including below scenario also].
```
[B]
SELECT * FROM `employee` SORT BY `Salary` DESC[/B] 
       EmpID   Salary 
               1    50000 
               3    50000 
               5    35000
               4    35000
               7    35000
              10    10000
              11    10000
                9     8500 
                2     7000         
                8     6000 
                6     5000
```
Next question: Can you tune this query to better solution? Because if you look from performance point of view , we are making two inner sql statements. Ideally, we should avoid such calls

0
PraveenKumar Purushothaman

@praveenkumar-66Ze92 • May 29, 2011

I have given a LIMIT=1. Did you notice that?

0
PraveenKumar Purushothaman

@praveenkumar-66Ze92 • May 29, 2011

Since it is gonna be a dynamic comparison, we need to get the two values dynamically by issueing a SELECT query, thereby making it two queries. There is no other way than that! 😔

0
sookie

@sookie-T06sFW • May 29, 2011

praveenscienceI have given a LIMIT=1. Did you notice that?
yep, It will give only 1 record but there can be many employees who have second highest salary. You will like them also to be displayed right?

0
PraveenKumar Purushothaman

@praveenkumar-66Ze92 • May 29, 2011

sookieyep, It will give only 1 record but there can be many employees who have second highest salary. You will like them also to be displayed right?
Dude! LIMIT=1 displays only one record. What do you wanna do?

0
sookie

@sookie-T06sFW • May 29, 2011

Ah ! My wrong ! I was lazy in loading an image and used your sample data code only. 😀 Hope this below image will clear what I was talking about - which employee details will get displayed - "CCC" or "DDD" or if any more employees with same second highest salary we have in data?

😀

0
PraveenKumar Purushothaman

@praveenkumar-66Ze92 • May 29, 2011

The query returns only the figure of the second max number. Then after that the selection will be random or as your next filter basis... 😛

0
BCA_GIRL

@bca-girl-wzX9cA • May 29, 2011

goyal420Consider a database which stores employee data having following fields

Name
Destination
Department
Salary

Now you have to write a sql query such that it will display the data of employee whose salary is second highest among all

Can you solve this?😛
Large function is used for this purpose.

0
ravi.shankar028

@ravishankar028-3fu26X • May 29, 2011

goyal420Consider a database which stores employee data having following fields

Name
Destination
Department
Salary

Now you have to write a sql query such that it will display the data of employee whose salary is second highest among all

Can you solve this?😛
The sql query is as:
SELECT * FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee) ORDER BY Salary DESC LIMIT 0,1;

0
PraveenKumar Purushothaman

@praveenkumar-66Ze92 • May 29, 2011
ravi.shankar028The sql query is as:
SELECT * FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee) ORDER BY Salary DESC LIMIT 0,1;
This query is same as what I said first! 😛 But he wants the salary group of people... In that case, make this query a subquery and send it to a main query where:
```
SELECT * FROM Employee WHERE Salary in (SELECT * FROM Employee WHERE Salary <  (SELECT MAX(Salary) FROM Employee) ORDER BY Salary DESC LIMIT 0,1);
```
This is way too complex then... 😔

0
David Chettiar

@david-chettiar-dOy0Co • Aug 4, 2011

select max(salary) from employee where salary <(select max(salary) from employee) This query will work... Actually this is an example of sub query..

0
PraveenKumar Purushothaman

@praveenkumar-66Ze92 • Aug 4, 2011

This doesn't work David... It always returns 0 rows! 😔

0
theinam

@theinam-h5R7nK • Aug 5, 2011

<i> friday 05:01pm </I>

SELECT MIN(SALARY) FROM (SELECT DISTINCT SALARY FROM EMPLOYEE ORDER BY SALARY DESC)WHERE ROWNUM=2;

you can change the rownum to get 3rd,4th,5th.... etc salary

0
PraveenKumar Purushothaman

@praveenkumar-66Ze92 • Aug 5, 2011

theinam<i> friday 05:01pm </I>

SELECT MIN(SALARY) FROM (SELECT DISTINCT SALARY FROM EMPLOYEE ORDER BY SALARY DESC)WHERE ROWNUM=2;

you can change the rownum to get 3rd,4th,5th.... etc salary
This works only with Oracle!

0
theinam

@theinam-h5R7nK • Aug 5, 2011

<to > praveen <t> sat 3:54pm </t>

i don`t think oracle is not an SQL server 😁
</to>

0
PraveenKumar Purushothaman

@praveenkumar-66Ze92 • Aug 5, 2011

theinam<to > praveen <t> sat 3:54pm </t>

i don`t think oracle is not an SQL server 😁
</to>
I am totally down!!! Someone please see and clarify what he says!!! 😲 Is that me or is it for real??? Dude, Oracle is a Database Server!!! 😐 Man!!!

0
theinam

@theinam-h5R7nK • Aug 6, 2011

<to> praveen <t> sat 4:30pm </t>

dude you seems to looking that
you are coming to argue with me or clarifying doubts ????????

and know that ORACLE is also be works like an SQL server.....

which college are you coming from 😎

</to>

0
PraveenKumar Purushothaman

@praveenkumar-66Ze92 • Aug 6, 2011

Fine... In which sense are you saying. Okay, well, say your views... 😀 I mean, I might be wrong. I am referring here the Oracle SQL Server. 😀

0
theinam

@theinam-h5R7nK • Aug 6, 2011

<to> praveen <t> 5:02pm </t>

think positively and don`t balm others .......

try to unique in your StYle but don`t say you should follow me...

i think you are the very good person with out having the common sense....

</to>

0
RajdeepCE

@rajdeepce-7UdrG8 • Aug 12, 2011

goyal420Consider a database which stores employee data having following fields

Name
Destination
Department
Salary

Now you have to write a sql query such that it will display the data of employee whose salary is second highest among all

Can you solve this?😛
For MS-SQL try following query,
SELECT *
FROM employee
WHERE Salary= ( SELECT DISTINCT Salary
FROM ( SELECT Salary,
ROW_NUMBER() OVER ( ORDER BY Salary DESC ) AS row
FROM employee
) a
WHERE row > 1
AND row <= 2
)

& for other DB, Limit will work I Guess (just my wild guess cause don't have any other DB in my machine),

SELECT *
FROM employee
WHERE Salary= ( SELECT DISTINCT Salary FROM employee LIMIT 1,2 ORDER BY Salary DESC )

Correct me if I am wrong. (I made it in hurry 😉 )

0
peter parker

@peter-parker-zGsYoF • Aug 22, 2011

select max(sal) from (select sal from emp where sal != (select max(sal) from emp))

0
peter parker

@peter-parker-zGsYoF • Aug 22, 2011

the above query is the solution for second highest salary

0
PraveenKumar Purushothaman

@praveenkumar-66Ze92 • Aug 22, 2011

Dude, what's the SQL Compatibility for your statement? Its not valid in MySQL, MSSQL or Oracle! 😔

0
srinivas_a9

@srinivas-a9-lTPv6Y • Aug 22, 2011

select * from employee d where 1 = (select count(1) from employee e where e.salary > d.salary)

0
PraveenKumar Purushothaman

@praveenkumar-66Ze92 • Aug 25, 2011
CEans, while posting, please mention the Database Server on which it would execute. For Eg.,
```
SHOW TABLES
```
doesn't execute on Oracle and
```
SELECT * FROM `tab`
```
doesn't execute on MySQL! 😁

0
Guravareddy

@guravareddy-Yi00Oq • Sep 29, 2011

select salary from employee e1 where n =(select count(e2.salary) from employee e2 where e1.salary<=e2.salary);

Here In this case n=2(for 2nd largest)

0
sachinswetha

@sachinswetha-Ko1jA4 • Jan 29, 2012

select * from employee where (select max(salary) from employee where rownum=2);

0
ravi.shankar028

@ravishankar028-3fu26X • Jan 30, 2012

sachinswethaselect * from employee where (select max(salary) from employee where rownum=2);
ROWNUM works for Oracle. This query cannot be used across all the databases. 😉

0
mnr

@mnr-hd8p8y • Jul 19, 2012

Display xyz(letters between - and -) from '123-xyz-abc' using sql or pl/sql

0
Bochare Rupali

@bochare-rupali-y03jvo • Jul 24, 2012

mohit007kumar00SELECT MAX(salary) FROM employee WHERE (salary!=MAX(salary));

am i right goyal...??
yes u r right goyal

0
Monikasingh

@monikasingh-dN3H3w • Oct 3, 2014

Select * from employee where sal= (select max (sal) from employee where sal not in ( select max (sal) from employee))

0
Prashant M Ahire

@prashant-m-ahire-ErLvai • Jan 11, 2016

SELECT salary FROM employee ORDER BY salary DESC LIMIT 1,1;
the easiest solution to find second highest salary of employee..

0
Prashant M Ahire

@prashant-m-ahire-ErLvai • Jan 11, 2016

if you want to find third highest salary then
SELECT salary FROM employee ORDER BY salary DESC LIMIT 2,1;

0