CODEWITHSUNDEEP
oracle-databasedategroup-by
Kabulan0lak
Kabulan0lak

Reputation: 2136

Having trouble to write an Oracle SQL query

I have a simple table T(NAME, DATE, VALUE)

What I want is to get the difference between VALUE of the row containing the oldest DATE and the one containing the earliest DATE, all this grouped by NAME.

I can't manage to write this into a single query.

Any help would be more than welcome, I have no idea how to perform this.

Upvotes: 0

Views: 18

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Here's one option; sample data is from line #1 - 8; query you might need begins at line #10.

SQL> with t(name, datum, value) as
  2  -- sample data
  3    (select 'LF', date '2020-01-01', 100 from dual union all
  4     select 'LF', date '2020-04-13', 200 from dual union all
  5     select 'LF', date '2020-05-26', 555 from dual union all
  6     --
  7     select 'ST', date '2020-03-13', 600 from dual
  8    )
  9  -- query
 10  select distinct
 11    name,
 12    maxval - minval diff
 13  from (select name,
 14          first_value(value) over (partition by name order by datum desc) maxval,
 15          first_value(value) over (partition by name order by datum asc)  minval
 16        from t
 17       );

NA       DIFF
-- ----------
LF        455
ST          0

SQL>

Upvotes: 1

Related Questions