CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-bygreatest-n-per-group
sgl
sgl

Reputation: 569

How to fetch rows with MAX condition involving multiple columns

I have an Employee table with the following columns:

Every employee can work in multiple projects at the same time.

I want the fetch all the records in the table for each employee with max ProjectStartDate. If an employee has multiple records with same ProjectStartDate then the record with max ProjectEndDate should be returned.

All dates in MM/DD/YYYY

enter image description here

Upvotes: 0

Views: 65

Answers (2)

The Impaler
The Impaler

Reputation: 48875

You can use the ROW_NUMBER() function, as in:

select *
from (
  select
    *,
    row_number() over(partition by emp_id 
      order by projectstartdate desc, projectenddate desc) as rn
  from employee
) x
where rn = 1

Upvotes: 0

GMB
GMB

Reputation: 222702

You can use ROW_NUMBER() in a subquery to rank the records of each employee by descending project start and end date, and then filter on the top record of each employee in the outer query:

SELECT * 
FROM (
    SELECT 
        e.*,
        ROW_NUMBER() OVER(PARTITION BY Emp_Id ORDER BY ProjectStartDate DESC, ProjectEndDate  DESC) rn
    FROM Employee e
) x
WHERE rn = 1

Upvotes: 2

Related Questions