SQL Server Performance

how to subtract values in consecutive rows?

Discussion in 'General Developer Questions' started by chopeen, Apr 23, 2005.

  1. chopeen Member

    There is a table with two columns - one of them is identity and the other contains some numeric values:

    id | value
    ---|-------
    1 | 10
    2 | 15
    3 | 35
    4 | 45
    5 | 50
    I need to subtract values in consecutive rows (value2 - value1, value3 - value2, value4 - value3, etc.) and get a table like this (the first row is not important - it can be NULL, 0 or whatever):

    id | diff
    ---|-------
    1 | NULL
    2 | 5
    3 | 20
    4 | 10
    5 | 5
    What is the best way to do it?

    --

    Rediscover the web
    http://www.mozilla.org/firefox/
  2. FrankKalis Moderator


    SET NOCOUNT ON
    IF OBJECT_ID('lfdsum_t') IS NOT NULL
    DROP TABLE lfdsum_t
    GO
    CREATE TABLE lfdsum_t
    (
    id INT IDENTITY
    , [value] int)
    INSERT INTO lfdsum_t values (10);
    INSERT INTO lfdsum_t values (15);
    INSERT INTO lfdsum_t values (35);
    INSERT INTO lfdsum_t values (45);
    INSERT INTO lfdsum_t values (50);

    SELECT
    ISNULL(a.id,b.id)
    , a.[value]-b.value
    FROM
    lfdsum_t a
    LEFT JOIN
    lfdsum_t b
    ON
    a.id-1=b.id
    DROP TABLE lfdsum_t
    SET NOCOUNT OFF

    ----------- -----------
    1 NULL
    2 5
    3 20
    4 10
    5 5

    If you don't really care about the first row, you can change the LEFT JOIN to an INNER JOIN which yields



    ----------- -----------
    2 5
    3 20
    4 10
    5 5


    --
    Frank Kalis
    SQL Server MVP
    http://www.insidesql.de
  3. ranjitjain New Member

    try this too...

    SELECT table1.id,(table1.value-t.value) as diff from table1
    inner join table1 as t on
    table1.id=t.id+1

    output will be

    id | diff
    2 5
    3 20
    4 10
    5 5
  4. ranjitjain New Member

    to get the first row value too...

    SELECT table1.id,isnull((table1.value-t.value),0) as diff from table1
    left join table1 as t on
    table1.id=t.id+1

    output will be
    id | diff
    10
    25
    320
    410
    55
  5. chopeen Member

    Thanks, guys! Your solutions work great.

    But sometimes the problem gets a little bit harder - the values in the id columnn aren't always continuous and they can be something like:
    id
    ---
    1
    2
    4
    6
    8
    9
    --

    Rediscover the web
    http://www.mozilla.org/firefox/
  6. FrankKalis Moderator

    Okay, I was afraid, you mentioned something like this. Well, off head I can only think of a workaround. Guess what...<br /><pre id="code"><font face="courier" size="2" id="code"><br />SET NOCOUNT ON<br />IF OBJECT_ID('lfdsum_t') IS NOT NULL<br /> DROP TABLE lfdsum_t<br />GO<br />CREATE TABLE lfdsum_t <br />(id INT <br />, [value] int)<br />INSERT INTO lfdsum_t values (1,10);<br />INSERT INTO lfdsum_t values (2,15);<br />INSERT INTO lfdsum_t values (3,35);<br />INSERT INTO lfdsum_t values (5,45);<br />INSERT INTO lfdsum_t values (7,50);<br />CREATE TABLE #workaround<br />(<br />ID_tmp INT IDENTITY<br />, ID INT<br />, [value] INT<br />)<br />INSERT INTO #workaround (id, [value])<br />SELECT id, [value] FROM lfdsum_t ORDER BY ID<br />SELECT<br /> a.id<br />, a.[value]-b.value<br />FROM<br /> #workaround a<br />LEFT JOIN<br /> #workaround b<br />ONa.id_tmp-1=b.id_tmp<br />DROP TABLE lfdsum_t, #workaround<br />SET NOCOUNT OFF<br /><br />id <br />----------- ----------- <br />1 NULL<br />2 5<br />3 20<br />5 10<br />7 5<br /><br /></font id="code"></pre id="code"><br />[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />--<br />Frank Kalis<br />SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  7. chopeen Member

    I was afraid an itermediate table will be necessary. I will try to create a nice multi-statement table-value function using your suggestions.

    It's a pity that IDENTITY(int, 1, 1) function works only with SELECT INTO statement and not with a regular SELECT.

    --

    Rediscover the web
    http://www.mozilla.org/firefox/
  8. FrankKalis Moderator

    You can use a CURSOR [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  9. FrankKalis Moderator

    FWIW, here's another solution. Can you test the performance for me?


    SET NOCOUNT ON
    IF OBJECT_ID('lfdsum_t') IS NOT NULL
    DROP TABLE lfdsum_t
    GO
    CREATE TABLE lfdsum_t
    (id INT
    , [value] int)
    INSERT INTO lfdsum_t values (1,10);
    INSERT INTO lfdsum_t values (2,15);
    INSERT INTO lfdsum_t values (3,35);
    INSERT INTO lfdsum_t values (5,45);
    INSERT INTO lfdsum_t values (7,50);

    SELECT
    a.id
    , [value]
    , [value] -( SELECT TOP 1 b.[value]
    FROM lfdsum_t b
    WHERE b.id < a.id
    ORDER BY b.id DESC ) x
    FROM
    lfdsum_t a
    DROP TABLE lfdsum_t
    SET NOCOUNT OFF


    id value x
    ----------- ----------- -----------
    1 10 NULL
    2 15 5
    3 35 20
    5 45 10
    7 50 5


    --
    Frank Kalis
    SQL Server MVP
    http://www.insidesql.de
  10. chopeen Member

    quote:Originally posted by FrankKalis

    FWIW, here's another solution. Can you test the performance for me?
    I've made some tests using a table with 5000 rows. It works great and the execution plan looks nice.

    --

    Rediscover the web
    http://www.mozilla.org/firefox/
  11. tc1 New Member

    I have a similar problem except I don't have an id column. Instead I want to use the row numbers to calculate consequtive valuse. Is this possible?
  12. FrankKalis Moderator

    [quote user="tc1"]
    I have a similar problem except I don't have an id column. Instead I want to use the row numbers to calculate consequtive valuse. Is this possible?
    [/quote]
    No problem, unless you have some column that can uniquely identify each row. Consider this:
    IF OBJECT_ID('lfdsum_t') IS NOT NULL
    DROP TABLE lfdsum_t
    GO
    CREATE TABLE lfdsum_t
    (key_col INT, [value] int)
    INSERT INTO lfdsum_t values (1,10);
    INSERT INTO lfdsum_t values (2,15);
    INSERT INTO lfdsum_t values (3,35);
    INSERT INTO lfdsum_t values (5,45);
    INSERT INTO lfdsum_t values (7,50);

    WITH myCTE (rn, [value])
    AS
    (SELECT ROW_NUMBER() OVER(ORDER BY key_col) AS rn, value
    FROM lfdsum_t)SELECT
    rn, [value] - (SELECT TOP 1 b.[value]
    FROM myCTE b
    WHERE b.rn < a.rn
    ORDER BY b.rn DESC)
    FROM myCTE aDROP
    TABLE lfdsum_t rn
    -------------------- -----------
    1 NULL
    2 5
    3 20
    4 10
    5 5
  13. tc1 New Member

    Thanks Frank. The only problem is I have an older version of SQL which doesn't have the ROW_NUMBER function. Do you know of an alternative way of doing this using SQL 8?
  14. FrankKalis Moderator

    [quote user="tc1"]
    Thanks Frank. The only problem is I have an older version of SQL which doesn't have the ROW_NUMBER function. Do you know of an alternative way of doing this using SQL 8?
    [/quote]
    Sorry, I thought you're using SQL Server 2005 because you mentioned row number. [:)]
    Anyway, as long as you have a primary (or unique) key in your table, you should be able to use the solutions mentioned earlier in this thread, such as:SELECT
    a.key_col, [value]
    , [value]
    -( SELECT TOP 1 b.[value]
    FROM lfdsum_t b
    WHERE b.key_col < a.key_col
    ORDER BY b.key_col DESC ) x
    FROM lfdsum_t a
    One "problem" however might be the performance on larger tables. If that is the case, you can workaround this by using a temp table in which you insert the rows in the order in which they should be processed for your calculations. Something like:CREATE
    TABLE #temp (key_col INT IDENTITY, [value] int)INSERT
    INTO #temp ([value]) SELECT [value]
    FROM lfdsum_t
    ORDER BY key_colSELECT
    a.key_col, [value]
    , [value]
    -( SELECT TOP 1 b.[value]
    FROM #temp b
    WHERE b.key_col < a.key_col
    ORDER BY b.key_col DESC ) x
    FROM #temp a
    Both methods should work in SQL Server 2000.
  15. tc1 New Member

    Thanks. That works fine. I think I'll use the second version.
    Rgds., TC.
  16. Madhivanan Moderator

    [quote user="tc1"]
    I have a similar problem except I don't have an id column. Instead I want to use the row numbers to calculate consequtive valuse. Is this possible?
    [/quote]
    Where do you want to show data?
    If you want to show them in front end, then you can calculate it when present data using while loop

Share This Page