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

From: Min Yin <yin(at)AI(dot)SRI(dot)COM>
To: pgsql-general(at)postgresql(dot)org
Subject: How do I write this query? Distinct, Group By, Order By?
Date: 2010-10-06 02:26:16
Message-ID: 4CABDE48.20302@ai.sri.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Matthew Hixson 2010-10-06 02:29:29 anyone have Movable Type 5 working with Postgres?
Previous Message Josh Kupershmidt 2010-10-06 00:56:10 Re: Postgres, DB2 and Materialized Query Tables (MQTs - DB2s Materialized Views)