Differences

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

--- adv_sample_user_queries_plus [2016/03/13 22:24] – daniel
+++ adv_sample_user_queries_plus [2021/05/15 12:21] (current) – daniel
@@ Line 1: / Line 1: @@
-==== Advanced examples of 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 25: / Line 25: @@
 <code sql>
 SELECT mail_id FROM mail m
-WHERE status&(16+32)=0
+WHERE status&32=0 AND status&16 -- intentionally split to benefit from the index on mail.status&32=0
 AND msg_date<now()-'1 month'::interval
 AND thread_id is not null
@@ Line 34: / Line 34: @@
 </code>
 ----
+=== Messages from the thread with the most unprocessed messages ===
+<code sql>
+select mail_id from mail where thread_id in
+(select thread_id
+  from mail
+   where status&32=0 and status&16=0
+     and thread_id is not null
+   group by thread_id
+   order by count(*) desc
+   limit 1
+)
+</code>
 See the [[http://www.manitou-mail.org/doc/mdx/database-schema.html|database schema documentation]] for definitions of tables and columns.