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

  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?


Many Thanks!

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

On Wed, Oct 6, 2010 at 3:34 AM, Min Yin <yin@ai.sri.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

  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
>
> ?
>
>

  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