How to list the maximum value of a column in between rows with a value duplicated?

Question

I have a table with 3 columns. I want make a SQL query that results in the maximum value in between the occurrences of DP_ID = A

ID	DP_ID	Value
1	A	10
2	B	264
3	B	265
4	B	266
5	A	10
6	B	115
7	B	116
8	A	25

The desired output would be:

ID	DP_ID	Value
4	B	266
7	B	116

I've tried to make a subquery to filter the table based on the ID of the rows that have DP_ID=A. The problem is that I can only retrieve one instance bye time. My desired output would list all the occurrences of it

Answer 1

Apache Derby is a quite limited database that does not support window functions or CTEs.

Note: I would strongly suggest you switch to H2 that has a similar footprint and offers much better capabilities.

The query becomes tediously long and non-performant in Derby, but can be written as:

select *
from (
  select
    t.*, 
    (select sum(case when dp_id = 'A' then 1 else 0 end) from t x
     where x.id <= t.id) as g
  from t
) z
where dp_id <> 'A'
  and value = (
  select max(value) as mv
  from (
    select
      t.*, 
      (select sum(case when dp_id = 'A' then 1 else 0 end) from t x 
       where x.id <= t.id) as g
    from t
  ) y
  where dp_id <> 'a' and y.g = z.g
  )

Result:

ID  DP_ID  VALUE  G
--  -----  -----  -
 4  B        266  1
 7  B        116  2

The data script that demonstrates this example is:

create table t(
   id    integer  not null
  ,dp_id varchar(2) not null
  ,value integer  not null
);

insert into t(id,dp_id,value) values (1,'A',10);
insert into t(id,dp_id,value) values (2,'B',264);
insert into t(id,dp_id,value) values (3,'B',265);
insert into t(id,dp_id,value) values (4,'B',266);
insert into t(id,dp_id,value) values (5,'A',10);
insert into t(id,dp_id,value) values (6,'B',115);
insert into t(id,dp_id,value) values (7,'B',116);
insert into t(id,dp_id,value) values (8,'A',25);