Hitesh Hitesh - 1 month ago 6
SQL Question

Find last record from distinct by EmpID in sql

I have a table in which I have EMPID column. In this table I have multiple row with same EMPID. Now I want to get last row of distinct EmpID. Please find below table data. And I want to result of Row with ID 3,5 and 7 with all column.

enter image description here

I want result as below :

enter image description here

I try with below query but is is not working.

SELECT ID,
EmpID,
Salary,
Leave,
TakenLeave,
field1,
field2,
field3
FROM Table_2
GROUP BY ID,
EmpID,
Salary,
Leave,
TakenLeave,
field1,
field2,
field3

Answer

Give a row_number partition by EmpId and order by Id desc.

Query

;with cte as(
    select rn = row_number() over(
        partition by EmpId
        order by Id desc
    ), *
    from [your_table_name]
)
select ID, EmpID, Salary, Leave, TakenLeave, field1, field2, field3 from cte
where rn = 1;
Comments