SQL: ORDER BY Before GROUP BY

Yii 1.1 General Discussion
1

Hey everyone,

I am trying to figure out the best way within Yii to have the ORDER BY performed before the GROUP BY for a SQL query.

I have a table of appointments as:




int      appt_id

int      customer_id

datetime appt_datetime

int      appt_length

Customers can have multiple appointments for each day.

I want a list of the first appointment for each customer scheduled on a certain day.

The SQL statement that would perform this uses a subquery:




SELECT * FROM (

     SELECT * FROM appointments

     WHERE DATE(appt_datetime) = DATE("2012-02-01")

     ORDER BY datetime

} as t1

GROUP BY customer_id

I’d just use the MIN(appt_datetime) if I needed just the appointment time, however I also need the length and appt_id of this appointment.

What is the best way to accomplish this in Yii.

I’m avoiding the use of findBySql() as I’d like to easily be able to change my DB system in the future and really don’t want to have to rewrite a boat load of SQL statements.

Thanks in advance,

C

2

Damned, I’m having the same problem to figure out how to make a CDbCriteria for a query like:


SELECT * FROM (

    SELECT * FROM message ORDER BY added_on DESC

) as m WHERE receiver_id = 2 GROUP BY sender_id ORDER BY priority_id DESC

Any help it’s welcome.

Cheers, Pablo.