Re: How do I write this query? Distinct, Group By, Order By?

On Wed, Oct 6, 2010 at 3:34 AM, Min Yin <yin(at)ai(dot)sri(dot)com> wrote:
>  Hi Yes that works too. Many Thanks!
>
> Now as you have probably , what I really want to get the full record of the
> user, which is in another table called users. The following query doesn't
> seem to work
>
> select users.id, users.* from users join orders on users.id=orders.user_id
> group by users.id order by max(orders.order_time) desc;
>
> If all I can get is a list of user_id, then can I get the list of user
> records in ONE 2nd query?

I bet there's other ways to do this, but this should work (you need
8.4 or later to use the WITH(...) clause):

WITH recent_users AS (
SELECT orders.user_id AS user_id, MAX(orders.order_time) AS max_order_time
FROM orders
GROUP BY orders.user_id
)
SELECT recent_users.max_order_time, users.*
FROM recent_users
INNER JOIN users
ON users.id = recent_users.user_id
ORDER BY recent_users.max_order_time;

Josh