CODEWITHSUNDEEP
sqlsql-servert-sql
Lawrence Wayne Ragudo
Lawrence Wayne Ragudo

Reputation: 43

SQL. Comparing value from current row versus the value resulted from the same comparison of the previous row

I am trying to create a script that would compare and get the higher value from current row versus the value resulted from the same comparison of the previous row.

Sample Table

For example:

  1. Row 2 Column 'Given' > Row 5 Column 'Result'. Therefore, Row 2 Column 'Result' gets the same value in Row 2 Column 'Given'

  2. Row 3 Column 'Given' < Row 2 Column 'Result'. Therefore, we will retain the value in Row 2 Column 'Result'. Same Thing with Row 4

  3. Rpw 5 Column 'Given' is now > Row 4 Column 'Result'. Therefore we will get the value in Row 5 Column.

My problem is it needs to be referenced on the same field that is yet to be created. Do you know any steps on how to attack this problem? Thank you!

Upvotes: 0

Views: 449

Answers (1)

gotqn
gotqn

Reputation: 43626

It seems that you are looking for something like this:

DECLARE @DataSource TABLE
(
    [RowID] INT
   ,[given] INT
);

INSERT INTO @DataSource ([RowID], [given])
VALUES (1, 10)
      ,(2, 15)
      ,(3, 12)
      ,(4, 14)
      ,(5, 20);

SELECT [given]
      ,MAX([given]) OVER (ORDER BY [RowID])
FROM @DataSource;

enter image description here

You can check the OVER clause as you may need to use PARTITION BY or ROWS BETWEEN in your real case.

Upvotes: 3

Related Questions