tag:blogger.com,1999:blog-8712770457197348465.post8479011033179664550..comments2024-03-28T02:47:19.159-07:00Comments on Javarevisited: How to find second highest or maximum salary of Employee in SQL - Interview questionjavin paulhttp://www.blogger.com/profile/15028902221295732276noreply@blogger.comBlogger26125tag:blogger.com,1999:blog-8712770457197348465.post-13993579386002913042020-03-21T22:51:08.594-07:002020-03-21T22:51:08.594-07:00Fourth highest Salary
create table Salary
(Name v...Fourth highest Salary<br /><br />create table Salary<br />(Name varchar(50) not null primary key,<br />Salaries int)<br /><br />insert into Salary<br />values('abc', 10000),<br />('bcd', 1000000),<br />('efg', 4000),<br />('ghi', 50000)<br /><br />select Top 1 Salaries from Salary<br />where Salaries IN <br />(Select Top 4 Salaries from Salary Order by Salaries Anonymoushttps://www.blogger.com/profile/16355610944715357236noreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-38687061004236619712019-10-23T17:13:57.369-07:002019-10-23T17:13:57.369-07:00SELECT *
FROM EMPLOYEES
ORDER BY SALARY DESC
OF...SELECT * <br />FROM EMPLOYEES <br />ORDER BY SALARY DESC <br />OFFSET 1 ROWS<br />FETCH NEXT 1 ROW ONLYSara tabainoreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-37096748650447411692019-07-14T14:00:19.321-07:002019-07-14T14:00:19.321-07:00I don't think u can write rownum=2 there
Acc t...I don't think u can write rownum=2 there<br />Acc to me solution can be<br />Select min(sal) from (Select sal from Emp order by sal desc)<br />where rownum < 3;Anonymoushttps://www.blogger.com/profile/17369875151678631806noreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-63955853317467467532016-10-05T08:54:40.324-07:002016-10-05T08:54:40.324-07:00good good Anonymoushttps://www.blogger.com/profile/04371854736790969722noreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-15786693678368793552016-08-17T23:26:18.708-07:002016-08-17T23:26:18.708-07:00For Nth max...
SELECT max(salary) from Employee e...For Nth max... <br />SELECT max(salary) from Employee e1<br /> WHERE N=select count(e2. Salary) from Employee e2 WHERE e1.salary <= e2. Salary <br />Anonymoushttps://www.blogger.com/profile/08391961166496117327noreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-83739636589287224452016-07-03T10:18:34.333-07:002016-07-03T10:18:34.333-07:00@sarvathi can u please update complete query, to f...@sarvathi can u please update complete query, to find the nth highest salary Anonymoushttps://www.blogger.com/profile/10639799405333126629noreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-17672364375429276712016-05-29T01:30:23.671-07:002016-05-29T01:30:23.671-07:00Well, it's best if you are using MySQL because...Well, it's best if you are using MySQL because LIMIT is not going to work on SQL Server, Oracle, or PostgreSQL.javin paulhttps://www.blogger.com/profile/15028902221295732276noreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-38833129615174778122016-05-17T08:52:22.664-07:002016-05-17T08:52:22.664-07:00Best Solution ...
SELECT salary FROM Employee ORDE...Best Solution ...<br />SELECT salary FROM Employee ORDER BY salary DESC LIMIT 2 ,1;<br /><br />Read more: http://code.shouttoday.comAnonymoushttps://www.blogger.com/profile/04478822842890371916noreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-36354121818719818052016-03-18T18:48:17.241-07:002016-03-18T18:48:17.241-07:00select top 1 b.* from employee a left join employe...select top 1 b.* from employee a left join employee b<br />on a.salary > b.salary<br />order by salary descAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-36592214565275596802015-11-05T01:49:59.302-08:002015-11-05T01:49:59.302-08:00how to find middle five eraners in salary from usi...how to find middle five eraners in salary from using of subquery Anonymoushttps://www.blogger.com/profile/02544887586351288319noreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-46270036263363923342015-10-16T08:37:27.336-07:002015-10-16T08:37:27.336-07:00@Gunjan . Your solution is really good. Can you el...@Gunjan . Your solution is really good. Can you elaborate your solution with explanation.Avinashhttps://www.blogger.com/profile/07424763657353866594noreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-65803337272567847882014-12-17T22:36:41.987-08:002014-12-17T22:36:41.987-08:00@Murali, that's the perfect way to find the th...@Murali, that's the perfect way to find the third highest salary. In fact, your solution uses correlated subquery and much more generic than normal solution without subquery, because you can just change the number near to WHERE clause e.g. WHERE 3 = (correlated query) to get third maximum, WHERE 4= () to get fourth largest and WHERE N = () to get Nth highest salary of employee. <br /><br />Servanthinoreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-71519181038772218522014-12-05T10:22:29.677-08:002014-12-05T10:22:29.677-08:00----is this correct way to find third highest sala...----is this correct way to find third highest salary <br /> SELECT ENAME,SALARY,DEPTNO<br /> FROM EMPLOYEE O<br /> WHERE 3=(SELECT COUNT(DISTINCT SALARY) FROM EMPLOYEE P<br /> WHERE O.SALARY<=P.SALARY)Anonymoushttps://www.blogger.com/profile/12154287728766749578noreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-79000875234255680492014-09-28T12:56:34.583-07:002014-09-28T12:56:34.583-07:00If you are looking for a pure sql query to find th...If you are looking for a pure sql query to find the nth highest salary, I found out this one to be the best<br />SELECT * FROM Employee Emp1 WHERE (N-1) = (SELECT COUNT(DISTINCT(Emp2.Salary))<br />FROM Employee Emp2<br />WHERE Emp2.Salary > Emp1.Salary)Gunjanhttps://www.blogger.com/profile/15137157730632208360noreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-975115374779107542014-09-15T05:15:25.918-07:002014-09-15T05:15:25.918-07:00SELECT salary FROM employee ORDER BY salary DESC L...SELECT salary FROM employee ORDER BY salary DESC LIMIT 1 , 1Anonymoushttps://www.blogger.com/profile/09921740332320162752noreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-8900390858682664252014-06-15T23:33:42.725-07:002014-06-15T23:33:42.725-07:00select * from employees where row_num=2 order by d...select * from employees where row_num=2 order by desc.<br />Is it correct in oracle. I dont have oracle data base. can some body tell meAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-30943317074138761242014-05-28T05:49:30.922-07:002014-05-28T05:49:30.922-07:00SELECT TOP 1 salary FROM ( SELECT TOP 3 salary FRO...SELECT TOP 1 salary FROM ( SELECT TOP 3 salary FROM employees ORDER BY salary DESC) AS emp ORDER BY salary ASC<br /><br />Is this correct for 3rd highest salary ??<br /><br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-12705595786658531852014-02-14T00:54:10.051-08:002014-02-14T00:54:10.051-08:00How to write SQL query for third highest salary? How to write SQL query for third highest salary? Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-90758272672047433202014-02-13T01:04:34.807-08:002014-02-13T01:04:34.807-08:00/*for 3rd highest salary n=2, for 2nd highest sala.../*for 3rd highest salary n=2, for 2nd highest salary n=1, nth highest salary n=n-1*/<br /><br />SELECT DISTINCT(Salary) FROM employee ORDER BY Salary DESC LIMIT n,1Anonymoushttps://www.blogger.com/profile/00215757443831118374noreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-28519838478822322462013-10-29T23:54:43.587-07:002013-10-29T23:54:43.587-07:00how about this?
Select salary from Employee order ...how about this?<br />Select salary from Employee order by salary desc limit 1,1;Sathish kumarhttps://www.blogger.com/profile/12755519366871754375noreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-89286579642304803062013-03-06T16:54:24.511-08:002013-03-06T16:54:24.511-08:00select max(salary) from employee where salary <...select max(salary) from employee where salary <any(select salary from employee)Terin sebhttps://www.blogger.com/profile/03294155167416339666noreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-64697002381355085502012-12-24T00:43:53.558-08:002012-12-24T00:43:53.558-08:00For Oracle
select * FROM (select salary , rownum...For Oracle<br /><br />select * FROM (select salary , rownum as RN from dbo.Employee order by salary desc) table1 where RN=2Neerjanoreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-37302724865086665812012-12-23T19:20:56.942-08:002012-12-23T19:20:56.942-08:00rownum() function is Oracle's equivalent of li...rownum() function is Oracle's equivalent of limit keyword of MySQL and can be used to find second highest, third highest or fourth highest salary or value in Oracle. By using rownum() you can give numbering to each record in resultset and than you can choose top two salaries based on condition in where clause. Here is right way to find Nth maximum salary in Oralce :<br /><br />select * from Sunidhinoreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-39879641967192942012012-12-23T17:43:18.455-08:002012-12-23T17:43:18.455-08:00Does method with TOP keyword to find second highes...Does method with TOP keyword to find second highest salary will also work on SQL Server 2005 and SQL Server 2008? I said yes but want to confirm it as I don't have SQL Server 2005 or 2008 installed on my machine to test this SQL query. Also Can you please share How to find Nth maximum salary in Oracle ?Rohannoreply@blogger.comtag:blogger.com,1999:blog-8712770457197348465.post-23499172882662934472012-12-23T01:34:41.550-08:002012-12-23T01:34:41.550-08:00@Gaurav, Thanks. Again use of distinct keyword to ...@Gaurav, Thanks. Again use of distinct keyword to only select unique salaries may or may not require based on problem statement.javin paulhttps://www.blogger.com/profile/15028902221295732276noreply@blogger.com