SQl query to get next employee salary
-
I have a table like below. Trying to achive the field "Other salary"
Here is the table Create table :
CREATE TABLE Employee2
(
E_ID integer, D_ID integer, salary integer)
--Truncate table Employee2
INSERT INTO Employee2(E_ID , D_ID, SALARY) VALUES (100, 201, 10000)
INSERT INTO Employee2(E_ID , D_ID, SALARY) VALUES (101, 201, 9500)
INSERT INTO Employee2(E_ID , D_ID, SALARY) VALUES (102, 201, 11000)
INSERT INTO Employee2(E_ID , D_ID, SALARY) VALUES (103, 205, 10500)
INSERT INTO Employee2(E_ID , D_ID, SALARY) VALUES (104, 205, 8000)
INSERT INTO Employee2(E_ID , D_ID, SALARY) VALUES (105, 205, 9500)
My Query:
;WITH CTE AS (
SELECT E.E_ID , E.D_ID, E.salary,Row_number() over (partition by d_id order by salary desc) as row
FROM Employee2 E
)
SELECT CurrentRow.* , Nextrow.row,Nextrow.Salary
from CTE CurrentRow
LEFT JOIn CTE Nextrow ON CurrentRow.E_ID=Nextrow.E_ID and Nextrow.row=Nextrow.row-1
Can anyone please fix my query 🙂 .
-
;WITH CTE AS (
SELECT E.E_ID , E.D_ID, E.salary,Row_number() over (partition by d_id order by salary desc) as row
FROM Employee2 E
)
SELECT CurrentRow.E_ID , CurrentRow.D_ID , Nextrow.Salary as othersalary
from CTE CurrentRow
left JOIn CTE Nextrow ON Currentrow.D_ID=nextrow.D_ID and CurrentRow.row=Nextrow.row-1
-
Instead of using row_number try using lead or lag
Thanks
-
Agree with @Taps.
LEAD()orLAG()is the best option here. Those functions were specifically designed to handle these types of queries.Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Something like this... I'm not sure my query is 100 percent correct, because I'm not sure what D_ID and E_ID mean.
SELECT D_ID
, E_ID
, Salary
, LAG(Salary, 1) OVER (PARTITION BY D_ID ORDER BY E_ID DESC) AS PrevSalary
FROM Employee2
ORDER BY D_ID, E_ID; -
Thank you everyone for the reply.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply