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@xxxxxxxxxx> 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
?
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
