SQLize | PHPize | SQLtest

CREATE TABLE client( id NUMBER(5), name VARCHAR2(30), constraint client_pk PRIMARY KEY (id)); CREATE TABLE transactions( "date" date, qty NUMBER(8,2), client_id NUMBER(5), constraint trsc_clnt_fk FOREIGN KEY (client_id) REFERENCES client(id) ); INSERT INTO client (id, name) values(1,'Вася'); INSERT INTO client (id, name) values(2,'Петя'); INSERT INTO client (id, name) values(3,'Коля'); INSERT INTO client (id, name) values(4,'Петя'); INSERT INTO transactions("date", qty, client_id) values(to_date('10.01.2010', 'dd.mm.yyyy'), 1000, 1); INSERT INTO transactions("date", qty, client_id) values(to_date('10.01.2010', 'dd.mm.yyyy') , 100, 4); INSERT INTO transactions("date", qty, client_id) values(to_date('13.01.2010', 'dd.mm.yyyy'), 400, 2); INSERT INTO transactions("date", qty, client_id) values(to_date('14.01.2010', 'dd.mm.yyyy'), 130, 4); INSERT INTO transactions("date", qty, client_id) values(to_date('15.01.2010', 'dd.mm.yyyy'), 1100, 2); INSERT INTO transactions("date", qty, client_id) values(to_date('17.01.2010', 'dd.mm.yyyy'), 1100, 3); INSERT INTO transactions("date", qty, client_id) values(to_date('10.01.2010', 'dd.mm.yyyy'), 110, 3); select * from transactions t1 where (t1.client_id, t1."date") in (select t.client_id ,sum(qty) over (partition by t.client_id order by "date") as sm_qty , max("date") over (partition by t.client_id) as max_date --,row_number() over (partition by t.client_id order by "date") as rn from transactions t)