Simple Query Question

I’m sure this is probably quite easy… but still not sure how to do it.

Here’s my Transactions Table

id | nextpaymentdate | user_id | processed_payments

---------------------------------------------------

 1 | 2011-06-10      |   2     |    1

 2 | 2011-07-10      |   2     |    2

 3 | 2011-05-19      |   13    |    1

 4 | 2011-06-19      |   13    |    2

 5 | 2011-07-19      |   13    |    3

I want to select a single record for each user_id with the largest number in the processed_payments column.

ie: How do I get this result set?

id | nextpaymentdate | user_id | processed_payments

---------------------------------------------------

 2 | 2011-07-10      |   2     |    2

 5 | 2011-07-19      |   13    |    3

This is my current criteria…

$criteria = new CDbCriteria;

$criteria->select = ‘user_id, cnextpaymentdate, MAX(cprocessedpayments) as maxpayment, id’;

$criteria->group = ‘user_id’;

But it’s not right…

Thanks for your help!

See topic Statistical Query in the guide.

public function relations()

{

 return array(

  'maxPaymentsProc'=>array(SELF::STAT, 'Transactions', 'processed_payments', 'select'=>'MAX(processed_payments) as maxPayProc'),

 );

}

Something like that should solve your problem

Well obviously it can be done using some nested php loop but I have no idea how you can do it using just a single line query

This article may help. (It’s for MySQL but works in other SQL dialects too, maybe with small syntactic changes.)

kokomo: that was exactly what I wanted. Worked (almost) perfectly first time.

phtamas: That article was good too, I got the query to work, but the relational query by Yii is cleaner and easier to use.

Thanks for your help guys.

Cheers

Tom

For anyone else looking at this topic, this is the relation I used in my users model…

‘maxPaymentsProc’=>array(self::STAT, ‘CbTransactions’, ‘user_id’, ‘select’=>‘MAX(processed_payments)’),