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

  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?

Thanks!


On 10/5/2010 8:45 PM, Josh Kupershmidt wrote:
> On Tue, Oct 5, 2010 at 10:26 PM, Min Yin<yin@ai.sri.com>  wrote:
>>   Hi There,
>>
>> I have a table looks like this:
>>
>> (order_id, user_id, order_time)
>>
>> One user_id can have multiple orders with order_id as the primary key, now I
>> want to get a list of users, ordered by their latest order respectively, for
>> example, if user A has two orders, one on today, the other a month ago, and
>> user B has one order a week ago, then the result should be
>>
>> A
>> B
>>
>> how do I do it? I tried various ways of SELECT with Distinct, Group By,
>> Order By, but was hit by either "column must appear in the GROUP BY clause
>> or be used in an aggregate function", or "for SELECT DISTINCT, ORDER BY
>> expressions must appear in select list" every time.
>>
>> Is it possible to do it? Is it possible to do it in one none-nested query?
>
> If all you need is the user_id, sorted by the timestamp of the user's
> most recent order, I think this should work:
>
> SELECT user_id FROM orders GROUP BY user_id ORDER BY MAX(order_time) DESC;
>
> Josh