Help with SQL - DDL and DML supplied. - Mailing list pgsql-novice
| From | Pál Teleki |
|---|---|
| Subject | Help with SQL - DDL and DML supplied. |
| Date | |
| Msg-id | CAMLfE0N=fHNhDLAZfz7UKdd-yAEhCPZZBEVNWiUNU-jZv5e4Yw@mail.gmail.com Whole thread Raw |
| List | pgsql-novice |
22;4;"aaaa";61;"2016-07-01 22:05:01"
14;1;"xxxx";57;"2016-07-01 22:05:01"
13;1;"xxxx";56;"2016-07-01 22:05:01"
15;1;"xxxx";56;"2016-07-15 22:05:01"
16;1;"xxxx";57;"2016-07-15 22:05:01"
17;2;"yyyy";58;"2016-07-01 22:05:01"
18;2;"yyyy";59;"2016-07-01 22:05:01"
19;2;"yyyy";58;"2016-07-17 22:05:01"
20;2;"yyyy";59;"2016-07-17 22:05:01"
21;3;"zzzz";60;"2016-07-01 22:05:01"
22;4;"aaaa";61;"2016-07-01 22:05:01"
15;1;"xxxx";56;"2016-07-15 22:05:01"
16;1;"xxxx";57;"2016-07-15 22:05:01"
19;2;"yyyy";58;"2016-07-17 22:05:01"
20;2;"yyyy";59;"2016-07-17 22:05:01"
21;3;"zzzz";60;"2016-07-01 22:05:01"
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.
old fashioned SQL only.
the other that ex_id is not necessarily in sync with the timestamp.
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.
It would be easy enough to do this with CTEs, but as I've said, the query also has to work
with MySQL.
SELECT t1.c_id AS theid1, t1.guid AS theguid1, t1.supplier, 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, t1.supplier
ORDER BY t1.guid, t1.supplier, MAX(t1.ts)
"theid1";"theguid1";"supplier";"mydate1"
4;"aaaa";61;"2016-07-01 22:05:01"
1;"xxxx";56;"2016-07-15 22:05:01"
1;"xxxx";57;"2016-07-15 22:05:01"
2;"yyyy";58;"2016-07-17 22:05:01"
2;"yyyy";59;"2016-07-17 22:05:01"
3;"zzzz";60;"2016-07-01 22:05:01"
Now, these are the records that **shouldn't** be deleted - 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.
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', 56, '2016-07-01 22:05:01');
insert into ex (c_id, guid, supplier, ts) VALUES(1, 'xxxx', 57, '2016-07-01 22:05:01');
insert into ex (c_id, guid, supplier, ts) VALUES(1, 'xxxx', 56, '2016-07-15 22:05:01');
insert into ex (c_id, guid, supplier, ts) VALUES(1, 'xxxx', 57, '2016-07-15 22:05:01');
insert into ex (c_id, guid, supplier, ts) VALUES(2, 'yyyy', 58, '2016-07-01 22:05:01');
insert into ex (c_id, guid, supplier, ts) VALUES(2, 'yyyy', 59, '2016-07-01 22:05:01');
insert into ex (c_id, guid, supplier, ts) VALUES(2, 'yyyy', 58, '2016-07-17 22:05:01');
insert into ex (c_id, guid, supplier, ts) VALUES(2, 'yyyy', 59, '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');
--
Pál Teleki
pgsql-novice by date: