-3

How to get max salary of 2 employees in each department in which employee is associated with?

APC
  • 144,005
  • 19
  • 170
  • 281
Shivani Gauryan
  • 103
  • 2
  • 12
  • 1
    What database are you using? Don't tag multiple DB's. Also show us what have you done till now. – Nitish Apr 10 '17 at 06:39
  • Instead of using rownum can we use rank , and if yes please share the query for the same. – Shivani Gauryan Apr 10 '17 at 06:44
  • Add some sample table data and the expected result - as well formatted text. Also show us your current query attempt. And remove tags for products not involved. – jarlh Apr 10 '17 at 06:46
  • @ShivaniGauryan - Rank will give you similar result if all salary in dept are different. But if they are same, they will be assigned same rank. However, row_number will not assign same row_number to same sal. It completely depends of your expectation from the query. Read this [article](https://blog.jooq.org/2014/08/12/the-difference-between-row_number-rank-and-dense_rank/) for details. – Utsav Apr 10 '17 at 07:12

1 Answers1

2

Something like this

select * from (
select employee_id,row_number() over (partition by department 
  order by salary desc) as rno 
 from employee
) t
where rno<=2
Utsav
  • 7,914
  • 2
  • 17
  • 38