#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    2
    Rep Power
    0

    Subtract previous row


    Hi, I need some help.

    I have this table "test" with fields:
    rownumber | id | hour | value1 | value 2 | date |
    1 | 2 | 2 | 5 | 100 | some date |
    2 | 1 | 2 | 10 | 120 | some date |
    3 | 1 | 3 | 35 | 220 | some date |
    4 | 1 | 4 | 50 | 360 | some date |
    5 | 2 | 3 | 21 | 400 | some date |
    6 | 2 | 4 | 35 | 600 | some date |
    . | 1 | . | . | . |
    . | 2 | . | . | . |
    . | 2 | . | . | . |
    n | 2 | n | n | n | some date |

    I need to write query that subtract previous value from the value after it for that id and insert results in new table with the date and hour from the row i subtract from.
    ex.
    value1 for hour 3 - value1 for hour 2 for ID 1 (10-5)
    value2 for hour 3 - value2 for hour 2 for Id 2 (220-120)
    value1 for hour 4 - value1 for hour 3 for Id 1 (360-220)
    and so on.

    Data will be put in the table hourly so when the date changes i need to restart the subtraction: data for hour 1 - data for hour 0, data for hour 2 - data for hour 1 .... data for hour 20 - data for hour 19 and so on.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    120
    Rep Power
    10
    it is not clear what you need but It looks like something like that will work for you

    Code:
    select t.*,
    (select top 1 value1 
    	from #test t1
    	where t1.id=t.id
    	and t1.hour > t.hour
    	order by t1.hour) - t.value1 as value1_1, 
    (select top 1 value2 
    	from #test t1
    	where t1.id=t.id
    	and t1.hour > t.hour
    	order by t1.hour) - t.value2 as value2_2 
    
    from test t
    order by id
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by gk53
    it is not clear what you need but It looks like something like that will work for you
    sorry, nope... mysql does not support TOP
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    120
    Rep Power
    10
    sure, I forgot it is mysql, so use limit 1


    select t.*,
    (select top 1 value1
    from #test t1
    where t1.id=t.id
    and t1.hour > t.hour
    order by t1.hour
    Limit 1) - t.value1 as value1_1,
    (select value2
    from #test t1
    where t1.id=t.id
    and t1.hour > t.hour
    order by t1.hour
    Limit 1) - t.value2 as value2_2

    from test t
    order by id
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    2
    Rep Power
    0
    Originally Posted by gk53
    sure, I forgot it is mysql, so use limit 1


    select t.*,
    (select top 1 value1
    from #test t1
    where t1.id=t.id
    and t1.hour > t.hour
    order by t1.hour
    Limit 1) - t.value1 as value1_1,
    (select value2
    from #test t1
    where t1.id=t.id
    and t1.hour > t.hour
    order by t1.hour
    Limit 1) - t.value2 as value2_2

    from test t
    order by id
    Thank You for your help, with minor modification of the query I solved the problem.

IMN logo majestic logo threadwatch logo seochat tools logo