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?

  • @mohammad2050 - the problem is how do you define the "first" and "last" rows. Is there some other column that defines what the order should be? For instance, is there an IDENTITY column or perhaps a DATETIME column that defines what the "first" and "last" rows are? – Max Vernon Dec 9 '15 at 18:11
  • 1
    yes , i have id column that is 1 to last and is IDENTITY column and tanks Max for edit my problem – mohammad2050 Dec 9 '15 at 18:13
20

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;
9

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) ;
1

Here is one way you could do that:

USE tempdb;

CREATE TABLE dbo.Test1
(
    ID INT NOT NULL
        CONSTRAINT PK_Test1 
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , Val INT NOT NULL
);
INSERT INTO dbo.Test1 (Val)
VALUES (1)
    , (2)
    , (3)
    , (50);

;WITH FirstAndLast
AS (
    SELECT t.ID
        , t.Val
        , RN  = ROW_NUMBER() OVER (ORDER BY ID)
        , RND = ROW_NUMBER() OVER (ORDER BY ID DESC)
    FROM dbo.Test1 t
)
SELECT TOP(1) l.Val - f.val
FROM FirstAndLast f
    INNER JOIN FirstAndLast l ON f.RN = l.RND

The idea here is to define the "first" and "last" rows. Once you have them defined, you can simply do the subtraction.

-2

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
    There's no guarantee that the Value column is always increasing. The identity column is, however. – RDFozz Nov 10 '18 at 18:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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