sample_user_queries
This is an old revision of the document!
Sample SQL queries
Messages that have a private note attached to them:
SELECT mail_id FROM notes
The last 20 messages that have a private note:
SELECT mail_id FROM notes ORDER BY mail_id DESC LIMIT 20
Messages that have a private note which has been added or modified during the last 7 days:
SELECT mail_id FROM notes WHERE last_changed>=now()-'7 days'::INTERVAL
Messages that have one or more pictures attached
SELECT mail_id FROM attachments WHERE content_type LIKE 'image/%'
Outgoing messages that have been composed less than one month ago
SELECT m.mail_id FROM mail m WHERE msg_date>=now()-'1 month'::INTERVAL AND status&256=256
Messages that have a lot of recipients (example: more than 20)
SELECT mail_id FROM mail_addresses WHERE addr_type=2 GROUP BY mail_id HAVING COUNT(*)>20
sample_user_queries.1350679471.txt.gz · Last modified: 2012/10/19 20:44 by daniel