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

On Tue, Oct 5, 2010 at 10:26 PM, Min Yin <yin(at)ai(dot)sri(dot)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