Re: Tricky SQL - assistance appreicated. DDL and DML supplied.

>> matt=# DELETE from ex where ex_id not in (SELECT t1.ex_id FROM ex t1 join (SELECT c_id, guid, max(ts) mts from ex group by c_id, guid) as t2 on t1.c_id = t2.c_id AND t1.guid = t2.guid AND t1.ts = t2.mts);
>> DELETE 4
>> matt=# SELECT * from ex;
>> ex_id | c_id | guid | supplier | ts
>> -------+------+------+----------+---------------------
>> 3 | 1 | xxxx | 50 | 2016-07-15 22:05:01
>> 4 | 1 | xxxx | 50 | 2016-07-15 22:05:01
>> 7 | 2 | yyyy | 71 | 2016-07-17 22:05:01
>> 8 | 2 | yyyy | 74 | 2016-07-17 22:05:01
>> 9 | 3 | zzzz | 60 | 2016-07-01 22:05:01
>> 10 | 4 | aaaa | 61 | 2016-07-01 22:05:01
>> (6 rows)

Thanks for that - it was what I was looking for.

>
> I believe this solution should work with mysql as well, although I've only done some light testing

Nope! :-) When one runs this query and ones like it in MySQL, it's charming
and quaint (ahem....) dialect of SQL produces the error:

ERROR 1093 (HY000): You can't specify target table 'ex' for update in
FROM clause

So, you have to add a level of nesting to your query to SELECT ex_id
FROM your derived table!
<shakes head.... how on earth did MySQL become more popular than PostgreSQL?>

As it was put so aptly here
(http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause),
"Sometimes I wonder what drugs the MySQL devs are on..." (found while
searching
for a solution - also to be found in that thread).

Thanks again.

--

Pál Teleki