-3

I want to find top 2 earners in each department

table => staff_masters having "staff_name" column for staffs and "dept_code" for corresponding department.

I tried to like this 

SELECT TOP 2 staff_sal 
FROM staff_masters
GROUP BY dept_code;

Please use group by clause.

Siyual
  • 16,415
  • 8
  • 44
  • 58
Shyam Sundar
  • 131
  • 1
  • 1
  • 7

2 Answers2

1

You would use row_number():

select sm.*
from (select dept_code, staff_sal,
             row_number() over (partition by dept_code order by staff_sal desc) as seqnum
      from staff_masters
     ) sm
where seqnum <= 2;

If you want distinct salaries, you would use dense_rank() rather than row_number().

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You can do this with a Row_Number() windowed function:

Select  Dept_Code, Staff_Sal
From
(
    Select  Dept_Code, Staff_Sal, 
            Row_Number() Over (Partition By Dept_Code Order By Staff_Sal Desc) As RN
    From    Staff_Masters
) A
Where   RN <= 2
Order By Dept_Code, RN
Siyual
  • 16,415
  • 8
  • 44
  • 58
  • hint for this task was "use GROUP BY clause". So how can I do that using that ? – Shyam Sundar Jul 03 '17 at 17:41
  • 1
    @ShyamSundar That's not what `GROUP BY` is designed for... `GROUP BY` is for *aggregation*... `ROW_NUMBER` is for ranking. You are not trying to perform aggregation, you are trying to perform ranking, so you need to use `ROW_NUMBER`. You are not going to be able to get that with `GROUP BY`. – Siyual Jul 03 '17 at 17:44
  • Ok then thanks for the e – Shyam Sundar Jul 03 '17 at 17:44