Solve this Sql Query

Replies

• 

  Leo

  Hey buddy is that your home work.
• 

  Manish Goyal

  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

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

  Morningdot Hablu

  SELECT 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 Goyal

  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

  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

  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

  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

  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

  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

  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

  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

  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

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

  PraveenKumar Purushothaman

  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

  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

  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

  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?
  
  
  😀
• 

  PraveenKumar Purushothaman

  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

  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

  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

  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

  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

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

  theinam

  friday 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

  theinam

  friday 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

  This works only with Oracle!
• 

  theinam

  praveen sat 3:54pm
  
  i don`t think oracle is not an SQL server 😁
  
  
  
• 

  PraveenKumar Purushothaman

  theinam

  praveen sat 3:54pm
  
  i don`t think oracle is not an SQL server 😁
  

  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

  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 Purushothaman

  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

  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

  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

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

  peter parker

  the above query is the solution for second highest salary
• 

  PraveenKumar Purushothaman

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

  srinivas_a9

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

  PraveenKumar Purushothaman

  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

  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

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

  ravi.shankar028

  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

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

  Bochare Rupali

  mohit007kumar00

  SELECT MAX(salary) FROM employee WHERE (salary!=MAX(salary));
  
  am i right goyal...??

  yes u r right goyal
• 

  Monikasingh

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

  Prashant M Ahire

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

  Prashant M Ahire

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