Lucky Makhado Lucky Makhado - 3 months ago 12
SQL Question

find the difference between previous row and current row

i have table presenters

enter image description here

i want to find the difference between presenters salary. like(Jason - vicki .jason - tiff. vicki - tiff)
i have the following code

SELECT P2.PNRFNAME ,P1.PNRFNAME AS "pnrfname" , P2.SALARY_YEARLY - P1.SALARY_YEARLY AS "salary difference"
FROM PRESENTERS P2
JOIN PRESENTERS P1
ON P2.PNRFNAME > P1.PNRFNAME
ORDER BY "salary difference"


this is the results

enter image description here

i want the following results

enter image description here

Answer

If your row are ordered by PRESENTERID, instead of doing P2.PNRFNAME > P1.PNRFNAME you can use PRESENTERID.

Try;

SELECT P2.PNRFNAME ,P1.PNRFNAME AS "pnrfname" , P2.SALARY_YEARLY - P1.SALARY_YEARLY AS "salary difference" 
FROM PRESENTERS P2
INNER JOIN PRESENTERS P1
ON P2.PRESENTERID> P1.PRESENTERID
ORDER BY P2.PRESENTERID, P1.PRESENTERID
Comments