Skip to Main Content
  • Questions
  • SQL query that returns the difference between the latest

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Swapnasis.

Asked: October 09, 2015 - 5:42 pm UTC

Last updated: January 29, 2018 - 12:02 pm UTC

Version: 10.1.2

Viewed 10K+ times! This question is

You Asked

Problem Statement

[question redacted]

Comment from Connor, Jan 29 1018
================================
You can see from our initial answer that were thrilled that you provided us a complete test case. However, when things seem too good to be true...often they indeed are.

We've just received a notification codility.com that your "question" was in fact stripped from their tests.

So Swapnasis, we're incredibly disappointed that you exploited us in this way, and wasted the time of others in the user community who also contributed an answer. We also offer our apologies to Codility who had their copyrighted work compromised.

and Connor said...

Thanks for the test script and test data - makes life easier. Its even prompted me to blog on something about your question

https://connormcdonald.wordpress.com/2015/10/10/why-i-love-working-on-asktom/

Anyway, here's some SQL for you

[redacted, please see question text above]

SQL>
SQL> commit;


First we add some analytics to the base query

SQL> select
  2    ttt.*,
  3    row_number() over ( partition by xx order by t123 desc ) as rk,
  4    lag(value) over ( partition by xx order by t123  ) as prev_val
  5  from ttt;

        XX      VALUE T123                                         RK   PREV_VAL
---------- ---------- ------------------------------------ ---------- ----------
         1          2 02-JAN-15 02.48.30.000000 PM                  1          7
         1          7 02-JAN-15 12.54.39.000000 PM                  2          5
         1          5 02-JAN-15 12.42.00.000000 PM                  3
         2         20 02-JAN-15 03.01.09.000000 PM                  1         16
         2         16 02-JAN-15 01.19.57.000000 PM                  2
         3        -42 02-JAN-15 01.19.57.000000 PM                  1

6 rows selected.


After that its easy to get the ones we want

SQL> with ranked_data as (
  2    select
  3      ttt.*,
  4      row_number() over ( partition by xx order by t123 desc ) as rk,
  5      lag(value) over ( partition by xx order by t123  ) as prev_val
  6    from ttt
  7  )
  8  select xx, value - prev_val as delta
  9  from ranked_data
 10  where rk = 1;

        XX      DELTA
---------- ----------
         1         -5
         2          4
         3


I've left the "3" in there, but if you dont want it, you simply add "prev_val is not null"

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Swapnasis Mohanty, October 10, 2015 - 6:34 am UTC

Thank's Connor.As I my new to SQL,I would really appreciate if you could add that Query "prev_val is not null" so that I would get the full solution and the desired output.

Connor McDonald
October 10, 2015 - 10:09 am UTC

SQL> with ranked_data as (
  2    select
  3      ttt.*,
  4      row_number() over ( partition by testid order by time desc ) as rk,
  5      lag(value) over ( partition by testid order by time  ) as prev_val
  6    from ttt
  7  )
  8  select testid, value - prev_val as delta
  9  from ranked_data
 10  where rk = 1;
 11  and prev_val is not null


For anyone with 12c

Stew Ashton, October 11, 2015 - 9:08 am UTC

Just in case someone with version 12c (or later) sees this question some day, the MATCH_RECOGNIZE clause provides the answer in one SELECT. You do need to get used to the syntax.
select * from test
match_recognize(
  partition by testid order by time desc
  measures a.value - b.value delta
  pattern(^a b)
  define a as 1=1,
         b as 1=1
);

    TESTID      DELTA
---------- ----------
         1         -5
         2          4

The '^' means "the beginning of a partition", so A refers to the first row and B to the second row. The DEFINE clause says what condition the row has to meet. In this case we want any row so the condition will always be true.

Best regards, Stew

P.S. I see you're back to showing line numbers as Tom did: nostalgia?
Connor McDonald
October 11, 2015 - 11:17 am UTC

I've always been a fan of the sqlplus output mode :-)


More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.