CODEWITHSUNDEEP
sqloracle-database
Mal_a
Mal_a

Reputation: 3760

SQL: Select all records when one column value change

I am having trouble to find the solution for this SQL Query.

Let me explain You on base of the table below:

   Group            DATUM    VALUE
    1002  2017-03-29 12:01:00     0.1
    1002  2017-03-29 12:02:36     0.2
    1002  2017-03-29 12:03:40     0.1
    1002  2017-03-29 12:04:44     0.2
    2523  2017-03-29 12:05:47     0.3
    2523  2017-03-29 12:06:51     0.1
    2523  2017-03-29 12:07:55     0.2
    2523  2017-03-29 12:08:59     0.3
    2523  2017-03-29 12:09:03     0.4
    3002  2017-03-29 12:10:07     0.5

I would like to get the data of every group. So on the base of table above the GROUP: 1002 has changed to GROUP: 2523, so i wanna receive all the records of GROUP: 1002, which is:

Group             DATUM    VALUE
1002  2017-03-29 12:01:00     0.1
1002  2017-03-29 12:02:36     0.2
1002  2017-03-29 12:03:40     0.1
1002  2017-03-29 12:04:44     0.2

and furtehrmore when the GROUP: 2523 is finished and the value changed to GROUP: 3002, i would get all the records for the GROUP: 2523 and so on...

The reason of doing this:

The raw data has to be read into another programme (KNIME) every time GROUP value changes (here is the part of SQL), then some specific values need to be calculated for each group, the new table has to be written in database.

Thanks for help!

Upvotes: 2

Views: 494

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

You would write a trigger to check whether a new insert has another group than the latest insert. In that case you'd have to call some routine that pushes the data to where you want it. Something like this:

create or replace trigger trg_latest_group_to_knime
before insert on mytable
for each row
declare
  v_last_group integer;
begin
  select max("GROUP") keep (dense_rank last order by datum)
  into v_group
  from mytable;

  if :new.group <> v_last_group then
    push_records_to_knime_for_group(v_last_group);
  end if;
end trg_latest_group_to_knime;

Upvotes: 1

Related Questions