sample_user_queries
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
sample_user_queries [2012/10/19 20:46] – daniel | sample_user_queries [2014/08/04 11:23] (current) – daniel | ||
---|---|---|---|
Line 1: | Line 1: | ||
==== Sample SQL queries ==== | ==== Sample SQL queries ==== | ||
- | Messages | + | See the [[http:// |
+ | |||
+ | === Messages | ||
<code sql> | <code sql> | ||
SELECT mail_id FROM notes | SELECT mail_id FROM notes | ||
</ | </ | ||
- | The last 20 messages that have a private note: | + | === The last 20 messages that have a private note === |
<code sql> | <code sql> | ||
SELECT mail_id FROM notes ORDER BY mail_id DESC LIMIT 20 | SELECT mail_id FROM notes ORDER BY mail_id DESC LIMIT 20 | ||
</ | </ | ||
- | Messages | + | === Messages |
<code sql> | <code sql> | ||
SELECT mail_id FROM notes WHERE last_changed> | SELECT mail_id FROM notes WHERE last_changed> | ||
</ | </ | ||
- | Messages | + | === Messages |
<code sql> | <code sql> | ||
select mail_id from attachments where content_type like ' | select mail_id from attachments where content_type like ' | ||
</ | </ | ||
- | Outgoing messages that have been composed less than one month ago | + | === Outgoing messages that have been composed less than one month ago === |
<code sql> | <code sql> | ||
SELECT m.mail_id FROM mail m WHERE msg_date> | SELECT m.mail_id FROM mail m WHERE msg_date> | ||
</ | </ | ||
- | Messages | + | === Messages |
+ | (example: more than 20 recipients) | ||
<code sql> | <code sql> | ||
SELECT mail_id FROM mail_addresses WHERE addr_type=2 GROUP BY mail_id having count(*)> | SELECT mail_id FROM mail_addresses WHERE addr_type=2 GROUP BY mail_id having count(*)> | ||
</ | </ | ||
- | Messages | + | === Messages |
+ | (example: more than 20) | ||
<code sql> | <code sql> | ||
SELECT mail_id FROM mail_addresses WHERE addr_type in (2,3) GROUP BY mail_id having count(*)> | SELECT mail_id FROM mail_addresses WHERE addr_type in (2,3) GROUP BY mail_id having count(*)> | ||
</ | </ | ||
+ | |||
+ | === Messages with any sender or recipient from a given domain === | ||
+ | <code sql> | ||
+ | select a.mail_id from mail_addresses a join addresses using(addr_id) where email_addr like ' | ||
+ | </ | ||
+ | |||
+ | === Messages with attachments bigger than a given size === | ||
+ | (example: bigger than 1Mb) | ||
+ | <code sql> | ||
+ | select mail_id from attachments where content_size > 1024*1024 | ||
+ | </ | ||
+ | |||
+ | ---- | ||
+ | |||
+ | See the [[http:// | ||
sample_user_queries.1350679573.txt.gz · Last modified: 2012/10/19 20:46 by daniel