Differences

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

--- adv_sample_user_queries_plus [2014/08/30 23:23] – daniel
+++ adv_sample_user_queries_plus [2016/03/13 22:24] – daniel
@@ Line 1: / Line 1: @@
-==== Advanced sample SQL queries ====
+==== Advanced examples of SQL queries ====
 === Messages sent, with senders or recipients from a specific domain, but not tagged with a specified tag: ===
@@ Line 22: / Line 22: @@
 </code>
+=== Old messages, not archived or trashed, that belong to a thread whose most recent message  is older than one month: ===
+<code sql>
+SELECT mail_id FROM mail m
+WHERE status&(16+32)=0
+AND msg_date<now()-'1 month'::interval
+AND thread_id is not null
+AND not exists
+ (SELECT 1 FROM mail m2
+  WHERE m2.thread_id=m.thread_id
+    AND m2.msg_date>now()-'1 month'::interval)
+</code>
 ----
 See the [[http://www.manitou-mail.org/doc/mdx/database-schema.html|database schema documentation]] for definitions of tables and columns.