CrazyEngineers Archive
Old, but evergreen and popular discussions on CrazyEngineers, presented to you in read-only mode.
@Manish Goyal • 02 Sep, 2010
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?😛
@Leo • 02 Sep, 2010 Hey buddy is that your home work.
@Manish Goyal • 02 Sep, 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
@bohar • 03 Sep, 2010 select max(salary) from employee where <(select max(salary) from employee)
@Morningdot Hablu • 03 Sep, 2010 SELECT MAX(salary) FROM employee WHERE (salary!=MAX(salary));

am i right goyal...??
@Manish Goyal • 03 Sep, 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);
@Morningdot Hablu • 03 Sep, 2010 @goyal but i think it will display all the salary which is less then max.

and where i am incorrect...??
@Manish Goyal • 03 Sep, 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".
@Morningdot Hablu • 03 Sep, 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.
@Manish Goyal • 03 Sep, 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
@nandini.arun • 28 Feb, 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);
@slashfear • 28 Feb, 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
@PraveenKumar Purushothaman • 02 Mar, 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 • 27 May, 2011
goyal420
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?😛
select max(salary)
from dabase
where salary <
(select max(salary) from database);
@PraveenKumar Purushothaman • 27 May, 2011
sinish
select 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 😁
@sookie • 30 May, 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
@PraveenKumar Purushothaman • 30 May, 2011 I have given a LIMIT=1. Did you notice that?
@PraveenKumar Purushothaman • 30 May, 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! 😔
@sookie • 30 May, 2011
praveenscience
I 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?
@PraveenKumar Purushothaman • 30 May, 2011
sookie
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?
Dude! LIMIT=1 displays only one record. What do you wanna do?
@sookie • 30 May, 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?
Data

😀
@PraveenKumar Purushothaman • 30 May, 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... 😛
@BCA_GIRL • 30 May, 2011
goyal420
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?😛
Large function is used for this purpose.
@ravi.shankar028 • 30 May, 2011
goyal420
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?😛
The sql query is as:
SELECT * FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee) ORDER BY Salary DESC LIMIT 0,1;
@PraveenKumar Purushothaman • 30 May, 2011
ravi.shankar028
The 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... 😔
@David Chettiar • 04 Aug, 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..
@PraveenKumar Purushothaman • 04 Aug, 2011 This doesn't work David... It always returns 0 rows! 😔
@theinam • 05 Aug, 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
@PraveenKumar Purushothaman • 05 Aug, 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!
@theinam • 06 Aug, 2011 <to > praveen <t> sat 3:54pm </t>

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


@PraveenKumar Purushothaman • 06 Aug, 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!!!
@theinam • 06 Aug, 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>
@PraveenKumar Purushothaman • 06 Aug, 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. 😀
@theinam • 06 Aug, 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>
@RajdeepCE • 12 Aug, 2011
goyal420
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?😛
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 😉 )
@peter parker • 22 Aug, 2011 select max(sal) from (select sal from emp where sal != (select max(sal) from emp))
@peter parker • 22 Aug, 2011 the above query is the solution for second highest salary
@PraveenKumar Purushothaman • 22 Aug, 2011 Dude, what's the SQL Compatibility for your statement? Its not valid in MySQL, MSSQL or Oracle! 😔
@srinivas_a9 • 23 Aug, 2011 select * from employee d where 1 = (select count(1) from employee e where e.salary > d.salary)
@PraveenKumar Purushothaman • 25 Aug, 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! 😁
@Guravareddy • 29 Sep, 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)
@sachinswetha • 29 Jan, 2012 select * from employee where (select max(salary) from employee where rownum=2);
@ravi.shankar028 • 30 Jan, 2012
sachinswetha
select * from employee where (select max(salary) from employee where rownum=2);
ROWNUM works for Oracle. This query cannot be used across all the databases. 😉
@mnr • 19 Jul, 2012 Display xyz(letters between - and -) from '123-xyz-abc' using sql or pl/sql
@Bochare Rupali • 25 Jul, 2012
mohit007kumar00
SELECT MAX(salary) FROM employee WHERE (salary!=MAX(salary));

am i right goyal...??
yes u r right goyal
@Monikasingh • 03 Oct, 2014 Select * from employee where sal= (select max (sal) from employee where sal not in ( select max (sal) from employee))
@Prashant M Ahire • 11 Jan, 2016 SELECT salary FROM employee ORDER BY salary DESC LIMIT 1,1;
the easiest solution to find second highest salary of employee..
@Prashant M Ahire • 11 Jan, 2016 if you want to find third highest salary then
SELECT salary FROM employee ORDER BY salary DESC LIMIT 2,1;
@supriya VN gowda • 20 Feb, 2020

Select * from employee where salary< ( select max(salary) from employee order by desc limit by 1.

5.7k views

Related Posts

@Coral Jain · Jun 19, 2015

Indian smartphone manufacturer Xolo has launched yet another flagship device called Xolo Cube 5.0 on Friday, just a month after the launch of the limited Liverpool FC edition Xolo One...
8.7k views

@Neda Hashmi · Sep 7, 2013

Here are some topic i came across while i was searching topic for myself. i still have to choose one. any suggestions for me? 1. Utilization Of Sludge Gas 2....
6.3k views

@Kaustubh Katdare · Nov 20, 2012

It turns out that engineering students have been searching for a project report format & sample. I found my old project report my team had created and decided to make...
10.3k views

@Kaustubh Katdare · Sep 14, 2015

Just created a whatsapp image that you can share with your friends and fellow engineers. Instead of just forward 'Happy Engineers Day', let's educate people about the reason.
4.7k views

@prabhat kumar · Apr 25, 2008

hi i m doing project with c and i want to get a mouse programing help me
5.4k views