Tricky SQL - assistance appreicated. DDL and DML supplied. - Mailing list pgsql-novice

Hi all,

I have an SQL problem and I'm stumped.


I have the following data (see DDL and DML at end of post)

"ex_id"; "c_id";   "guid"; "supplier";                      "ts"
-------- -------  -------- -----------     ---------------------
   42;        4;   "aaaa";         61;     "2016-07-01 22:05:01"
   34;        1;   "xxxx";         50;     "2016-07-01 22:05:01"
   33;        1;   "xxxx";         54;     "2016-07-01 22:05:01"
   35;        1;   "xxxx";         50;     "2016-07-15 22:05:01"
   36;        1;   "xxxx";         50;     "2016-07-15 22:05:01"
   37;        2;   "yyyy";         78;     "2016-07-01 22:05:01"
   38;        2;   "yyyy";         79;     "2016-07-01 22:05:01"
   39;        2;   "yyyy";         71;     "2016-07-17 22:05:01"
   40;        2;   "yyyy";         74;     "2016-07-17 22:05:01"
   41;        3;   "zzzz";         60;     "2016-07-01 22:05:01"


and the resulting table - i.e. the records I want to retain in the table are:


"ex_id";   "c_id";   "guid"; "supplier";                      "ts"
--------   -------  -------- -----------     ---------------------
     42;        4;   "aaaa";         61;     "2016-07-01 22:05:01"
     35;        1;   "xxxx";         50;     "2016-07-15 22:05:01"
     36;        1;   "xxxx";         50;     "2016-07-15 22:05:01"
     39;        2;   "yyyy";         71;     "2016-07-17 22:05:01"
     40;        2;   "yyyy";         74;     "2016-07-17 22:05:01"
     41;        3;   "zzzz";         60;     "2016-07-01 22:05:01"



i.e. Take the combination of c_id, guid and date which is the last, no matter
how many records there are with a given combination of same. The (guid) 'aaaa'
and the 'zzzz' records obviously stay since there's only 1 of each.

But the records with 'xxxx' and 'yyyy' have to be "pruned" - only keep those
with the latest date where c_id matches and guid matches.

1) This query will also have to work with MySQL, so CTEs and Analytic functions
are out - good old fashioned SQL only.

2) Ideally, I would like two queries, one under the assumption that ex_id is
sequential with time and the other that ex_id is not necessarily in sync with
the timestamp.

3) This appears to be a "top-n-per-group" problem of some sort - I would
appreciate an explanation of the query, not just the raw query itself - I'm
trying to learn rather than just a bald answer. References, URLs &c appreciated.

Just to show that I'm not totally lazy :-), I have formulated what I think is
the first step, but I'm stuck. It would be easy enough to do this with CTEs but,
as I've said, the query also has to work with MySQL. If you want to throw in

solutions based on CTEs and/or SET operators and/or Analytic functions,

for pedagological purposes, great, but I do need a MySQL solution.

SELECT t1.c_id AS theid1, t1.guid AS theguid1, MAX(t1.ts) AS mydate1 FROM ex t1
INNER JOIN ex t2
  ON t1.c_id = t2.c_id AND t1.guid = t2.guid
GROUP BY t1.c_id, t1.guid
ORDER BY t1.guid, MAX(t1.ts)

"theid1";   "theguid1";               "mydate1"
---------   -----------   ---------------------
       4;       "aaaa";   "2016-07-01 22:05:01"
       1;       "xxxx";   "2016-07-15 22:05:01"
       2;       "yyyy";   "2016-07-17 22:05:01"
       3;       "zzzz";   "2016-07-01 22:05:01"



Now, these are the records that **shouldn't** be deleted - without
the supplier data - which also has to be retained - i.e. I need to
retain more records than this - but this should be a start. The data

are correct for guid, datetime and c_id.

I just can't figure out how to formulate the statement to delete the
other records.

Maybe with SET operators, if MySQL had those?

Anyway, all and any input appreciated. If any further info. is required, please
let me know.


============= DDL and DML for the table ===============


CREATE TABLE ex
(
  ex_id serial,
  c_id int,
  guid varchar(32),
  supplier int,
  ts timestamp,
  PRIMARY KEY (ex_id)
);



insert into ex (c_id, guid, supplier, ts) VALUES(1, 'xxxx', 54, '2016-07-01 22:05:01');
insert into ex (c_id, guid, supplier, ts) VALUES(1, 'xxxx', 50, '2016-07-01 22:05:01');
insert into ex (c_id, guid, supplier, ts) VALUES(1, 'xxxx', 50, '2016-07-15 22:05:01');
insert into ex (c_id, guid, supplier, ts) VALUES(1, 'xxxx', 50, '2016-07-15 22:05:01');


insert into ex (c_id, guid, supplier, ts) VALUES(2, 'yyyy', 78, '2016-07-01 22:05:01');
insert into ex (c_id, guid, supplier, ts) VALUES(2, 'yyyy', 79, '2016-07-01 22:05:01');
insert into ex (c_id, guid, supplier, ts) VALUES(2, 'yyyy', 71, '2016-07-17 22:05:01');
insert into ex (c_id, guid, supplier, ts) VALUES(2, 'yyyy', 74, '2016-07-17 22:05:01');

insert into ex (c_id, guid, supplier, ts) VALUES(3, 'zzzz', 60, '2016-07-01 22:05:01');
insert into ex (c_id, guid, supplier, ts) VALUES(4, 'aaaa', 61, '2016-07-01 22:05:01');