-
Hey buddy is that your home work.
Are you sure? This action cannot be undone.
-
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
Are you sure? This action cannot be undone.
-
bohar
Member •
Sep 2, 2010
select max(salary) from employee where <(select max(salary) from employee)
Are you sure? This action cannot be undone.
-
SELECT MAX(salary) FROM employee WHERE (salary!=MAX(salary));
am i right goyal...??
Are you sure? This action cannot be undone.
-
@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);
Are you sure? This action cannot be undone.
-
@goyal but i think it will display all the salary which is less then max.
and where i am incorrect...??
Are you sure? This action cannot be undone.
-
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".
Are you sure? This action cannot be undone.
-
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.
Are you sure? This action cannot be undone.
-
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
Are you sure? This action cannot be undone.
-
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);
Are you sure? This action cannot be undone.
-
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
Are you sure? This action cannot be undone.
-
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 5000Till 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 35000Oracle 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 35000I guess this could be useful!
Are you sure? This action cannot be undone.
-
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);
Are you sure? This action cannot be undone.
-
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 😁
Are you sure? This action cannot be undone.
-
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
Are you sure? This action cannot be undone.
-
I have given a LIMIT=1. Did you notice that?
Are you sure? This action cannot be undone.
-
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! 😔
Are you sure? This action cannot be undone.
-
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?
Are you sure? This action cannot be undone.
-
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?
Are you sure? This action cannot be undone.
-
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?
😀
Are you sure? This action cannot be undone.
-
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... 😛
Are you sure? This action cannot be undone.
-
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.
Are you sure? This action cannot be undone.
-
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;
Are you sure? This action cannot be undone.
-
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... 😔
Are you sure? This action cannot be undone.
-
select max(salary) from employee where salary <(select max(salary) from employee) This query will work... Actually this is an example of sub query..
Are you sure? This action cannot be undone.
-
This doesn't work David... It always returns 0 rows! 😔
Are you sure? This action cannot be undone.
-
<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
Are you sure? This action cannot be undone.
-
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!
Are you sure? This action cannot be undone.
-
<to > praveen <t> sat 3:54pm </t>
i don`t think oracle is not an SQL server 😁
</to>
Are you sure? This action cannot be undone.
-
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!!!
Are you sure? This action cannot be undone.
-
<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>
Are you sure? This action cannot be undone.
-
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. 😀
Are you sure? This action cannot be undone.
-
<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>
Are you sure? This action cannot be undone.
-
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 😉 )
Are you sure? This action cannot be undone.
-
select max(sal) from (select sal from emp where sal != (select max(sal) from emp))
Are you sure? This action cannot be undone.
-
the above query is the solution for second highest salary
Are you sure? This action cannot be undone.
-
Dude, what's the SQL Compatibility for your statement? Its not valid in MySQL, MSSQL or Oracle! 😔
Are you sure? This action cannot be undone.
-
select * from employee d where 1 = (select count(1) from employee e where e.salary > d.salary)
Are you sure? This action cannot be undone.
-
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! 😁
Are you sure? This action cannot be undone.
-
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)
Are you sure? This action cannot be undone.
-
select * from employee where (select max(salary) from employee where rownum=2);
Are you sure? This action cannot be undone.
-
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. 😉
Are you sure? This action cannot be undone.
-
mnr
Member •
Jul 19, 2012
Display xyz(letters between - and -) from '123-xyz-abc' using sql or pl/sql
Are you sure? This action cannot be undone.
-
mohit007kumar00
SELECT MAX(salary) FROM employee WHERE (salary!=MAX(salary));
am i right goyal...??
yes u r right goyal
Are you sure? This action cannot be undone.
-
Select * from employee where sal= (select max (sal) from employee where sal not in ( select max (sal) from employee))
Are you sure? This action cannot be undone.
-
SELECT salary FROM employee ORDER BY salary DESC LIMIT 1,1;
the easiest solution to find second highest salary of employee..
Are you sure? This action cannot be undone.
-
if you want to find third highest salary then
SELECT salary FROM employee ORDER BY salary DESC LIMIT 2,1;
Are you sure? This action cannot be undone.
