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

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

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!

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