Solve this Sql Query
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?๐
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?๐
Replies
-
LeoHey buddy is that your home work.
-
Manish Goyalno 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 -
boharselect max(salary) from employee where <(select max(salary) from employee)
-
Morningdot HabluSELECT MAX(salary) FROM employee WHERE (salary!=MAX(salary));
am i right goyal...?? -
Manish Goyal@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@goyal but i think it will display all the salary which is less then max.
and where i am incorrect...?? -
Manish GoyalWhen 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
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 Goyalno 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.arunHi 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); -
slashfeargoyal420@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 PurushothamanHey 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
select max(salary)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?๐
from dabase
where salary <
(select max(salary) from database); -
PraveenKumar Purushothaman
This doesn't work... No salary is greater than the MAX salary... So, a small correction... ๐sinishselect max(salary)
from dabase
where salary <
(select max(salary) from database);
make it salary = MAX(salary) and LIMIT = 1 ๐ -
sookieI 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 PurushothamanI have given a LIMIT=1. Did you notice that?
-
PraveenKumar PurushothamanSince 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
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?praveenscienceI have given a LIMIT=1. Did you notice that? -
PraveenKumar Purushothaman
Dude! LIMIT=1 displays only one record. What do you wanna do?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? -
sookieAh ! 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?
๐ -
PraveenKumar PurushothamanThe 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
Large function is used for this purpose.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?๐ -
ravi.shankar028
The sql query is as: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 * FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee) ORDER BY Salary DESC LIMIT 0,1; -
PraveenKumar Purushothaman
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:ravi.shankar028The sql query is as:
SELECT * FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee) ORDER BY Salary DESC LIMIT 0,1;
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 Chettiarselect max(salary) from employee where salary <(select max(salary) from employee) This query will work... Actually this is an example of sub query..
-
PraveenKumar PurushothamanThis doesn't work David... It always returns 0 rows! ๐
-
theinamfriday 05:01pm
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
This works only with Oracle!theinamfriday 05:01pm
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 -
theinam
praveen sat 3:54pm
i don`t think oracle is not an SQL server ๐
-
PraveenKumar Purushothaman
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!!!theinampraveen sat 3:54pm
i don`t think oracle is not an SQL server ๐
-
theinam
praveen sat 4:30pm
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 ๐
-
PraveenKumar PurushothamanFine... 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
praveen 5:02pm
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....
-
RajdeepCE
For MS-SQL try following query,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 *
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 parkerselect max(sal) from (select sal from emp where sal != (select max(sal) from emp))
-
peter parkerthe above query is the solution for second highest salary
-
PraveenKumar PurushothamanDude, what's the SQL Compatibility for your statement? Its not valid in MySQL, MSSQL or Oracle! ๐
-
srinivas_a9select * from employee d where 1 = (select count(1) from employee e where e.salary > d.salary)
-
PraveenKumar PurushothamanCEans, while posting, please mention the Database Server on which it would execute. For Eg.,
SHOW TABLES
doesn't execute on Oracle andSELECT * FROM `tab`
doesn't execute on MySQL! ๐ -
Guravareddyselect 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) -
sachinswethaselect * from employee where (select max(salary) from employee where rownum=2);
-
ravi.shankar028
ROWNUM works for Oracle. This query cannot be used across all the databases. ๐sachinswethaselect * from employee where (select max(salary) from employee where rownum=2); -
mnrDisplay xyz(letters between - and -) from '123-xyz-abc' using sql or pl/sql
-
Bochare Rupali
yes u r right goyalmohit007kumar00SELECT MAX(salary) FROM employee WHERE (salary!=MAX(salary));
am i right goyal...?? -
MonikasinghSelect * from employee where sal= (select max (sal) from employee where sal not in ( select max (sal) from employee))
-
Prashant M AhireSELECT salary FROM employee ORDER BY salary DESC LIMIT 1,1;
the easiest solution to find second highest salary of employee.. -
Prashant M Ahireif you want to find third highest salary then
SELECT salary FROM employee ORDER BY salary DESC LIMIT 2,1;
You are reading an archived discussion.
Related Posts
A Russian website got access to IE 9's screenshot. The site has removed the information however ZeDNet folks grabbed it before it was lost. Microsoft will release the beta version...
Little Boy Banku is mad at his teacher, who ate all the sweets in the flat and even went to the neighbours to eat their sweets too. Now Banku's parents...
Skype has released new beta version 5 of its video calling software. In a blog post, the Skype officials say -
Today weโve released an update to Skype for Windows....
hello moderator,
sir i m pursuing btech in it field. Now i m in 3rd year.Can u suggest me whether certificate matters in the incampus or offcampus placements? I have...
HP raised its offer by $3 to $33 per share, beating Dell's latest $32-a-share offer and ending an exchange of bids that escalated to levels that many analysts said was...