CodePudding user response:

CodePudding user response:

 USE tempdb for 
GO 
IF OBJECT_ID (' project ') IS NOT NULL 
DROP TABLE project 
GO 
The CREATE TABLE project (
Project_id INT, 
Employee_id INT 
) 
GO 
INSERT INTO project VALUES (1, 1) 
INSERT INTO project VALUES (1, 2) 
INSERT INTO project VALUES (1, 3) 
INSERT INTO project VALUES (2, 1) 
INSERT INTO project VALUES (2, 4) 
GO 
IF OBJECT_ID (" employee ") IS NOT NULL 
DROP TABLE employee 
GO 
The CREATE TABLE employee (
Employee_id INT, 
[name] NVARCHAR (20), 
Experience_years INT 
) 
GO 
INSERT INTO the employee VALUES (1, 'Khaled, 3) 
INSERT INTO the employee VALUES (2, 'Ali, 2) 
INSERT INTO the employee VALUES (3, "John", 3) 
INSERT INTO the employee VALUES (4, 'Doe', 2) 
GO 
SELECT * 
The FROM (
The SELECT Rank () OVER (PARTITION BY p.p roject_id ORDER BY e.e xperience_years DESC) AS rids 
, p.p roject_id 
, e.e mployee_id 
, e. [name] 
, e.e xperience_years 
FROM the project AS p INNER JOIN the employee AS e ON p.e mployee_id=e.e mployee_id 
) AS t 
WHERE t.r id=1 
/* 
Rids project_id employee_id name experience_years 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
1 1 1 Khaled 3 
1 1 3 John 3 
1 2 1 Khaled 3 
*/

CodePudding user response: