Amazon Interview Question
Software Engineer / DevelopersBe careful with this answer. A number of interviews I've been on asked this same question with the stipulation that you do it only in standard SQL. "Top" is only valid on MSSQL. "Limit" is for MySQL. "Rownum" is for Oracle. All of these are technically non-standard SQL.
The answer they are looking for is use self joins.
Sumanth has it correct.
I think question also states about the result in each group. So small addition to your SQL should make it right.
Select Top 2 Salary
From Salary_Table
GROUP BY group_field
Order BY Salary DESC;
select * from emp_tab where ( select count(*) from emp_tab st where st.sal < emp_tab.sal ) <= n ; ( here n is 2 )
This will not give the correct result..here is the new one:
select * from Emp_Table where
(select count(*) from Emp_Table ET where ET.salary > Emp_Table.salary
and ET.groupID=Emp_Table.groupid) <=1
This will give the top 2 records with top 2 salary from the table emp.
select * from emp where emp.sal >=
(select max(emp.sal) from emp where emp.sal <> (select max(emp.sal) from emp));
I am not that familiar with writing complex sql queries,
but
1) Recursive queries?
2) select from another select?
When we have the 'top' command, which is highly likely to have been optimized by the Database, why does one have to go and try to come up with silly queries which are likely to be inefficient?
Correctness is not the only virtue...
select max(sal) from emp
- yins September 27, 2009union
select max(sal) from emp where sal not in (select max(sal) from emp)