10

I have a table with a value column. I want to calculate the last row minus the first row, as shown here:

 id      value
  1       10
  2       45
  3       65
  4       95
  .       .
  .       .
  .       .
 500     200

I want to obtain 200 - 10 = 190

I've tried to use the below command in SQL Server 2012, however LAST and FIRST don't work.

SELECT LAST(Value) - FIRST(Value) FROM Counter;

What is the syntax for this command in SQL Server?

0

3 Answers 3

21

You were close - FIRST and LAST are from Access; in SQL Server (starting with SQL Server 2012) they are FIRST_VALUE() and LAST_VALUE().

So, if you are 2012 or better (or Azure SQL Database), here's one way to get your answer:

CREATE TABLE #fl
(
  IdentityColumn INT IDENTITY, 
  Value INT
);

INSERT #fl(Value) SELECT 10;
INSERT #fl(Value) SELECT 45;
INSERT #fl(Value) SELECT 65;
INSERT #fl(Value) SELECT 95;
INSERT #fl(Value) SELECT 200;

SELECT TOP (1) LAST_VALUE(Value) OVER (ORDER BY IdentityColumn)
            - FIRST_VALUE(Value) OVER (ORDER BY IdentityColumn)
  FROM #fl
  ORDER BY IdentityColumn DESC;

GO
DROP TABLE #fl;
0
10

One more way (that works in older versions as well):

SELECT 
    result = (SELECT TOP (1) value FROM counter ORDER BY id DESC)
             - (SELECT TOP (1) value FROM counter ORDER BY id ASC) ;
-3

Why don't use the MAX and Min Function (Optional if you have Criteria use Where)

Eg. Select (Max(NumFieldName) - Min(NumFieldName)) AS Output FROM TableName

1
  • 1
    There's no guarantee that the Value column is always increasing. The identity column is, however.
    – RDFozz
    Nov 10, 2018 at 18:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.