Differences

This shows you the differences between two versions of the page.

--- adv_sample_user_queries_plus [2014/07/22 13:18] – created daniel
+++ adv_sample_user_queries_plus [2014/08/30 23:23] – daniel
@@ Line 1: / Line 1: @@
 ==== Advanced sample SQL queries ====
-Messages sent, with senders or recipients from a specific domain, but not tagged with a specified tag:
+=== Messages sent, with senders or recipients from a specific domain, but not tagged with a specified tag: ===
 <code sql>
@@ Line 14: / Line 14: @@
 </code>
+=== Latest outgoing message per distinct recipient (to which a message was ever addressed): ===
+<code sql>
+select mail_id from (select distinct on (m1.addr_id) m.mail_id,m1.addr_id
+ from mail_addresses m1 join mail m using(mail_id)
+  where m1.addr_type=2 and m.status&129=129
+  order by m1.addr_id,m.msg_date desc) s
+</code>
 ----