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