CODEWITHSUNDEEP
sqlpostgresql
pmichna
pmichna

Reputation: 4888

Sort by left joined table

I have orders table and order_histories table. I need to sort orders by order_histories.created_at but only those order_histories that have state_to = 'submitted'.

An order has multiple order_histories and may have none submitted. It may also have multiple submitted - in that case I'm interested in the last one (hence MAX(order_histories.created_at)).

What I have come up with is below. However, this is not sufficient because orders without submitted order_history are not included.

SELECT orders.*, MAX(order_histories.created_at) AS date
FROM orders
LEFT JOIN order_histories ON order_histories.order_id = orders.id
WHERE order_histories.state_to = 'submitted'
GROUP BY orders.id
ORDER BY date ASC, orders.id DESC

How can I make it correct?

Upvotes: 0

Views: 44

Answers (2)

Joe Taras
Joe Taras

Reputation: 15389

In this way, you'll get all orders

  • If exists one order with state submitted it returns its date
  • If you have more order_histories with state submitted it returns only the max date

  • If you have an order without submitted state you'll have NULL in that date (the spirit of LEFT OUTER JOIN)

  • If you want to discard orders without state submitted specify it so I'll change the query

Try this:

SELECT orders.*, 
(SELECT MAX(oh.created_at) 
FROM order_histories oh
WHERE oh.order_id = orders.id
AND oh.state_to = 'submitted') AS date
FROM orders
ORDER BY date ASC, orders.id DESC

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

Criteria on an outer-joined table belongs in the ON clause not in the WHERE clause:

SELECT orders.*, MAX(order_histories.created_at) AS date
FROM orders
LEFT JOIN order_histories ON  order_histories.order_id = orders.id
                          AND order_histories.state_to = 'submitted'
GROUP BY orders.id
ORDER BY date ASC NULLS LAST, orders.id DESC;

Using a WHERE clause instead rendered your join a mere inner join, because it dismissed all outer-joined rows (i.e. those without a submitted order history record).

Upvotes: 2

Related Questions