0

I have two tables.

Salary_Grade

GRADE Min_Salary Max_Salary
12 2100 3600
13 3601 4200
14 4201 6000
15 6001 9000
16 9001 30000

Employees

EMPLOYEE_NO NAME HIRE_DATE SALARY
1007 SMITH 2016-02-20 00:00:00.000 15000
2340 JOHNSON 2018-02-07 00:00:00.000 3300
2341 WILLIAMS 2019-10-11 00:00:00.000 3750
2345 BROWN 2018-01-01 00:00:00.000 8925
2355 JONES 2015-07-13 00:00:00.000 8550
3434 GARCIA 2011-08-11 00:00:00.000 7350
4356 MILLER 2013-10-12 00:00:00.000 3750
4455 DAVIS 2000-04-30 00:00:00.000 2850
4456 WILSON 1980-03-03 00:00:00.000 9000
4467 ANDERSON 2001-07-28 00:00:00.000 3900
5643 THOMAS 2011-03-10 00:00:00.000 4800
6538 TAYLOR 2011-08-11 00:00:00.000 9000
6578 MOORE 2020-11-27 00:00:00.000 2400
8900 LEE 2015-03-03 00:00:00.000 4500

My task is to display the two employees with the longest work experience, for each GRADE (the grade is results from the salary range in the SALARY_GRADE and the corresponding SALARY from the EMPLOYEE table)

Expected result:

GRADE NAME EXPERIENCE(DAYS)
12 JOHNSON 1359
12 DAVIS 7851
13 MILLER 2938
13 ANDERSON 7397
14 THOMAS 3885
14 LEE 2431
15 WILSON 15214
15 TAYLOR 3731
16 SMITH 2077

I created table EMPLOYEE_SALGRADE with employee id and salary grades connected to them

   CREATE TABLE [EMPLOYEE_SALGRADE](

   [GRADE_NO] [int]  not null,
   [EMPLOYEE_NO] [int]  not null,
   FOREIGN KEY (Grade_NO) REFERENCES Salary_Grade(grade),
   FOREIGN KEY (Employee_NO) REFERENCES Employee(Employee_NO))

   insert into EMPLOYEE_SALGRADE(GRADE_NO, EMPLOYEE_NO)
   SELECT s.grade, e.EMPLOYEE_NO FROM employee as e,salary_grade as s
   WHERE e.salary BETWEEN s.min_salary AND s.max_salary
   order by e.salary'

and added column Experience to Employee table

Alter table Employee
add Experience as  DATEDIFF(dd,Hire_date,getdate())

Now I'm trying with subquery

select  s.GRADE, e.NAME,  e.Experience
from SALARY_GRADE as S
join EMPLOYEE_SALGRADE AS ES
ON S.GRADE=es.GRADE_NO
join EMPLOYEE as e 
on es.Employee_no=e.EMPLOYEE_NO
where Experience in (select top 2(experience) from EMPLOYEE group by Experience)

But this not correct result

Rafał Rymuza
  • 1
  • 2
  • 2
    I've removed the tag [[tag:mysql]] as this uses brackets (`[]`) to delimit identify, which is what T-SQL uses. If you *are* using MySQL, you should be using backticks (`\``). – Thom A Oct 28 '21 at 10:39
  • 2
    Side note: It's **2021**, it's *long* past time you adopted the use of the ANSI-92 explicit JOIN syntax in all your statements, not just some of them. – Thom A Oct 28 '21 at 10:45
  • 2
    For your subquery, a `TOP 2` with no `ORDER BY` will mean that 2 **arbitrary** rows will be returned. These rows could be different *every time* the query is run. Adding some expected results here will likely help us help you. – Thom A Oct 28 '21 at 10:59
  • I would recommend that you look at RANK: https://learn.microsoft.com/en-us/sql/t-sql/functions/rank-transact-sql?view=sql-server-ver15 – Danieboy Oct 28 '21 at 11:05
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Charlieface Oct 28 '21 at 12:48

1 Answers1

0

I did some research, and the correct answer is:

select * from ( select  s.GRADE, e.NAME,  e.Experience,  row_number() over (partition by s.grade order by e.experience desc) as employee_rank
from SALARY_GRADE as S
join EMPLOYEE_SALGRADE AS ES
ON S.GRADE=es.GRADE_NO
join EMPLOYEE as e 
on es.Employee_no=e.EMPLOYEE_NO) ranks
where employee_rank <= 2;
ouflak
  • 2,458
  • 10
  • 44
  • 49
Rafał Rymuza
  • 1
  • 2