Могу кусок кода привести, с выборки сообщений по диалогам:
$criteria = new CDbCriteria;
$criteria->select = "t.* ,(SELECT COUNT(*) FROM {{messages}} WHERE IF(t.userTo=:userid,userTo,userFrom)=t.userTo AND IF(t.userTo=:userid,userFrom,userTo)=t.userFrom AND isNew=1) AS newMessagesdialog";
$criteria->join = "JOIN (SELECT IF(userTo=:userid,userFrom,userTo) AS user_id_other, MAX(creationDate) AS date_time_max FROM pdb_messages
WHERE (userTo=:userid OR userFrom=:userid) AND IF(userTo=:userid,hideOnUserTo,hideOnUserFrom)=0 AND moderated_status=1 AND IF(type='s' AND userFrom=:userid,false,true) GROUP BY IF(userTo=:userid,userFrom,userTo)) AS d ON IF(userTo=:userid,userFrom,userTo)=user_id_other AND creationDate=date_time_max ";
$criteria->addCondition('userTo=:userid OR userFrom=:userid');
$criteria->order = 'newMessagesdialog DESC,t.creationDate DESC';
$criteria->params = array(':userid' => $userid);
Код заточен под Yii, но в общем если разобраться то суть в выборке видна