Postgres - multi-column group - wizard / part type table

Question

I have a table order (orderid, ordernumber, clientid, orderdesc, etc.) and the corresponding status for this order in the order_status table (statusid, orderid, statusdesc, statusNote, statustimestamp)

Say I have a record as below

orderid orderumber clientid orderdesc 1111 00980065 ABC blah..blah.. 

and corresponding status records

 statusid orderid statusdesc statusNote statustimestamp 11 1111 recvd status blah yyyy-mm-dd:10:00 12 1111 clientproce status blah yyyy-mm-dd:11:00 13 1111 clientnotice status blah yyyy-mm-dd:15:00 14 1111 notified status blah yyyy-mm-dd:17:00 

How can I get the following result (last timestamp along with multiple columns)

 1111 14 00980065 ABC blah..blah.. notified status blah yyyy-mm-dd:17:00 

Answer 1

Postgresql gives you a much simpler alternative to multiple subqueries with its (non-standard SQL) DISTINCT ON :

 SELECT DISTINCT ON (o.orderid) o.orderid, s.statusid, o.clientid, o.orderdesc, s.statusdesc, s.statusnote, s.statustimestamp FROM order o, status s WHERE s.orderid = o.orderid ORDER BY o.orderid, s.statustimestamp DESC; 

This assumes that each order has at least one status . In another place, and if you want to show also orders without status, you would make an external connection.

Answer 2

It:

 SELECT (o).*, (s).* FROM ( SELECT o, ( SELECT s FROM status s WHERE s.orderid = o.orderid ORDER BY statustimestamp DESC LIMIT 1 ) s FROM orderid o ) q 

or that:

 SELECT DISTINCT ON (o.orderid) * FROM orders o LEFT JOIN status s ON s.orderid = o.orderid ORDER BY o.orderid, s.statustimestamp DESC 

The first one is more effective if you have many statuses per order, the last one - several statuses per order.

For the first query to work quickly, create an index on status (orderid, statustimestamp)