postgres - group by on multiple columns - master/detail type table

Question

I have a table order(orderid, ordernumber, clientid, orderdesc etc.,) and a corresponding status for that order on an order_status table ( statusid, orderid, statusdesc, statusNote, statustimestamp)

say I have a record in order as below

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

and a corresponding status entries

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 (latest timestamp along with multiple columns)

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

Answer 1

This:

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 this:

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 former one is more efficient if you have lots of statuses per order, the latter one if few statuses per order.

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

Answer 2

Postgresql gives you a much simpler alternative to the multiple subselects with its (not 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 every order has at least one status . Elsewhere, and if you want to show also orders without status, you'd do an outer join.