Solve this Sql Query -Part 2

Replies

• 

  ISHAN TOPRE

  I know a little. Is it
  
  Select * from employee where Salary< salary1, salary 2, salary 3, salary4
  
  ?? I do not know much but we can define salary 1,2,3,4. In this way we can get the highest salaried employee's data too.
• 

  Sahithi Pallavi

  @ishu : that's not the right answer I guess because if we want the details of the employee having 20th highest salary, then its difficult to mention all the numbers..
  
  I don't know the answer, I am trying now!
• 

  synergynext

  SELECT * FROM table_name a
  WHERE 5 = (SELECT COUNT(rowid)
  FROM table_name b
  WHERE a.rowid >= b.rowid);
   

  a and b are alias of same table. you can replace 5 by any number you want. This type of questions is very popular for freshers' interviews 😀
• 

  synergynext

  Correction:
  didnt read highest salary earlier:
  
  correct solution is as follows:
  
  

  SELECT MIN(salary)
  FROM (SELECT salary FROM employees ORDER BY salary DESC)
  WHERE ROWNUM <= 5;
  

• 

  Manish Goyal

  That's nice answer,
  I want single query ,you can modify it dynamically in order to get right answer.
• 

  synergynext

  SELECT rownum, salary FROM employee 
  GROUP BY rownum, salary HAVING rownum = 5
   

  enjoy
• 

  Manish Goyal

  synergynext

  SELECT rownum, salary FROM employee
  GROUP BY rownum, salary HAVING rownum = 5
  
  enjoy

  Ok say i have deleted employee data whose id is 5 ,then will this query work?
• 

  synergynext

  I hope you are aware of "What Rownum is"!
• 

  Manish Goyal

  synergynext

  I hope you are aware of "What Rownum is"!

  hmm actually i am aware of Mysql only ,but this query is not working there,
• 

  synergynext

  hmmm,
  If you have mysql installed, can you try this one and let me know if it works
  

   
  SELECT * FROM employee ORDER BY salary DESC LIMIT 5,4
  

• 

  synergynext

  or LIMIT 4, 5
  whichever works haha
• 

  synergynext

  goyal420

  Ok say i have deleted employee data whose id is 5 ,then will this query work?

  rownum is a pseudo column in Oracle sql plus. Suppose if you run the query that uses rownum, and delete that record. Now when you retry the query, you will get new output which will be 5th highest salary at that time.
• 

  sookie

   select * from (
  select name,salary,dense_rank() over(order by salary desc) dr
  from employee
  ) where dr = :rankOrder
  

  Enter rankOrder as 5 to get the output .
• 

  synergynext

  His initial requirement was to have a single query, not a nested one.
  In my opinion this one is the answer:
  

  SELECT rownum, salary FROM employee 
  GROUP BY rownum, salary HAVING rownum = 5
  

  Later he asked it for MySQL, so this would run fine on MySQL.
  

  SELECT * FROM employee ORDER BY salary DESC LIMIT 4,5 
  

  Both are single queries.
• 

  sookie

  @synergynext "rownum" fails when you have two employees having same salary say '35000' as 5th highest salary. It will give you one record always in spite of having many two.
• 

  PraveenKumar Purushothaman

  I guess the best SQL Query would be:
  

  SELECT * FROM `emp` GROUP BY `salary` DESC LIMIT 4,5