I want to get the updated Salary by EmpID-CodePudding

I have a column EmpID and Salary. I want to get the updated salary each EmpID.

FIGURE 1 :

ID	EMPID	Salary
1	00000	75000
2	00000	80000
3	00001	75000
4	00001	90000
5	00001	91000
6	00010	50000
7	00010	60000
8	00010	70000

This is my initial query that I created with specific EMPID 00000 :

Select DISTINCT EmpID,Salary from Table1 as t1 WHERE  Salary != '0.00' AND EXISTS 
 (SELECT TOP 1 Salary ,EmpID From Table1 where Table1.EmpID = t1 ORDER BY ID DESC) AND EmpID = '00000'

The output that I get is still the same as Figure 1.

The output must be in distinct in EmpID with the updated salary.

/****** Sub query example  ******/
/****** This is the output I would like to display  ******/
Select TOP 1 Salary FROM Table1 Where EMPID = '00000' ORDER BY ID DESC
Select TOP 1 Salary FROM Table1 Where EMPID = '00001' ORDER BY ID DESC
Select TOP 1 Salary FROM Table1 Where EMPID = '00010' ORDER BY ID DESC

FIGURE 2 :

ID	EMPID	Salary
1	00000	80000
2	00001	91000
3	00010	70000

CodePudding user response：

what are you looking for can be achieved by group by as follows, however chosing your desired ID remain issue but it has been acheived by row_number

SELECT Row_number()
         OVER (
           ORDER BY empid) AS ID,
       empid,
       Max(salary)         Salary
FROM   table1
GROUP  BY empid

in addition you can use Row_number and Sub-query in order to distinguish new changes.

SELECT ROW_NUMBER() OVER (ORDER BY EmpID) AS ID,EmpID, Salary
FROM   (SELECT ID,
               empid,
               salary,
               Row_number()
                 OVER (
                   partition BY empid
                   ORDER BY salary DESC) AS RowNumber
        FROM   table1) T
WHERE  rownumber = 1