SQl query to get next employee salary

• komal145

  SSCrazy Eights

  Points: 9918

  More actions

  July 18, 2019 at 8:48 pm

  #3664317

  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 .

   

   

• komal145

  SSCrazy Eights

  Points: 9918

  More actions

  July 18, 2019 at 9:34 pm

  #3664336

  ;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

• @Taps

  SSC Eights!

  Points: 879

  More actions

  July 19, 2019 at 8:16 am

  #3664435

  Instead of using row_number try using lead or lag

  Thanks

• drew.allen

  SSC Guru

  Points: 77006

  More actions

  July 19, 2019 at 2:03 pm

  #3664538

  Agree with @Taps.  LEAD() or LAG() 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

• pietlinden

  SSC Guru

  Points: 63551

  More actions

  July 19, 2019 at 2:24 pm

  #3664541

  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;

• komal145

  SSCrazy Eights

  Points: 9918

  More actions

  July 19, 2019 at 4:36 pm

  #3664586

  Thank you everyone for the reply.