Group question in postgresql

Question

Say that I have “orders” tables created as:

CREATE TABLE orders (id SERIAL, customerID INTEGER, timestamp BIGINT, PRIMARY KEY(id)); 

The timestamp is a UNIX timestamp. Now I want to select LATEST order IDs for each customer. How nice it would be.

however, the following statement

 CREATE VIEW lastOrders AS SELECT id, customerID, MAX(timestamp) FROM orders GROUP BY customerID; 

Throws a postgre error:

ERROR: column "orders.id" should appear in GROUP BY or use in aggregate function

What am I doing wrong?

Answer 1

For this kind of thing, you can use 2 approaches. Jens has already shown.

Another is to use the "DISTINCT ON" clause:

 CREATE VIEW lastOrders AS SELECT DISTINCT ON (customerID) id, customerID, timestamp FROM orders ORDER BY customerID, timestamp desc; 

Answer 2

The following request should only return the most recent order for each customer.

 CREATE VIEW last_orders AS SELECT id, customer_id, timestamp FROM orders AS o WHERE timestamp = ( SELECT MAX(timestamp) FROM orders AS oi WHERE o.customer_id = oi.customer_id ); 

(Assuming you cannot have two orders for a customer with the same timestamp value.)

Edit: Postgres DISTINCT ON is a much more convenient way to do this. I'm glad I found out about this. But the above work is for other RDBMSs.

Answer 3

Kquinns answer will fix the exception, but not what you are looking for.

I do not know the features of mysql, but something like this will work with oracle:

 select a.* from orders a, (select customerID, max(timestamp) timestamp from orders group by customerID ) b where a.customer_id = b.customerID and a.timestamp = b.timestamp 

actually using oracle you can use analytic functions but i don't think they are available in mysql