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

  Yes that works. Thanks a lot!

Now what if I want to get not only user_id, but 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.*, max(orders.order_time) from users join orders
on users.id=orders.user_id group by users.id order by
max(orders.order_time) desc;

Also I'm using JPA+Hibernate, is it possible to get a List of Users
objects as the query result? I'm not sure with max(order_time) in the
select list, what will be returned.

Thanks!


On 10/5/2010 8:29 PM, Peter Hunsberger wrote:
> On Tue, Oct 5, 2010 at 9: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?
>>
>>
> It's not clear what order time is, but is there any reason you can't just do
>
> select user_id, max(order_time) from whatever group by user_id
>
> ?
>
>