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
