Find the difference between previous row and current row

I have a table presenters

enter image description here

  • Downloading a file after storing it in SQL Server 2008 Data Base
  • Partition SQL table on monthly basis
  • Multiple result sets excluding column
  • Locking statement won't compile with INNER JOIN present
  • SQL Server ROWLOCK over a SELECT if not exists INSERT transaction
  • SQL Server - Select first row that meets criteria
  • 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"
    

    These are the results

    enter image description here

    But I want the following results

    enter image description here

    2 Solutions collect form web for “Find the difference between previous row and current row”

    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
    

    You can try this code:

    WITH CTE
    AS (
        SELECT rownum = ROW_NUMBER() OVER (
                ORDER BY PNRFNAME
                )
            ,PNRFNAME
            ,SALARY_YEARLY
        FROM PRESENTERS
        )
    SELECT prev.PNRFNAME PreviousValue
        ,CTE.PNRFNAME
        ,prev.SALARY_YEARLY - CTE.SALARY_YEARLY AS SALARY_DIFF
    FROM CTE
    LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
    GO
    

    Source: How to Access the Previous Row and Next Row value in SELECT statement?

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.