From: | Pál Teleki <ellenallhatatlan(at)gmail(dot)com> |
---|---|
To: | |
Cc: | pgsql-novice novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Help with SQL - DDL and DML supplied. |
Date: | 2016-07-02 21:57:43 |
Message-ID: | CAMLfE0N=fHNhDLAZfz7UKdd-yAEhCPZZBEVNWiUNU-jZv5e4Yw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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)
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"
and the result I want is
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"
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.
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.
============= 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', 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
From | Date | Subject | |
---|---|---|---|
Next Message | Pál Teleki | 2016-07-03 00:05:52 | Tricky SQL - assistance appreicated. DDL and DML supplied. |
Previous Message | Bob Cochran | 2016-07-01 21:16:27 | Re: initdb failing on REL9_5_STABLE with exit code 132... |