I am trying to run the following query:
SELECT customers.id, customers.name, o.order_date FROM customers, (SELECT id, order_date FROM orders ORDER BY order_date DESC) as o WHERE o.id = ( SELECT id FROM o WHERE customer_id = customers.id LIMIT 1) ORDER BY customer_id;
However it results with an error
relation "o" does not exist
I understand that there is an issue with an alias of a subquery above it but I don’t know how can I fix it. The reason why I’m not referencing "orders" table directly in WHERE clause is because I want to optimise it by creating a subquery with just 2 columns which are ordered, so this query is not executed on entire table each time a comparison in WHERE clause is made (is it how it works?).
Is there any other way I can write this query?
That’s not the only problem with your query – you’re also referencing
customer_id that you did not select from
orders. Once you fix that and move your subquery to a CTE, it’ll work:
with o as ( SELECT id, customer_id, order_date FROM orders ORDER BY order_date DESC) SELECT customers.id, customers.name, o.order_date FROM customers, o WHERE o.id = ( SELECT id FROM o WHERE o.customer_id = customers.id LIMIT 1) ORDER BY customer_id;
When I am joining these tables I want to join only first match from orders table based on date (the most recent one)
MAX() would be sufficient to get customers and their most recent order dates.
SELECT c.id, c.name, max(o.order_date) as most_recent_order_date FROM customers c left join orders o on o.customer_id=c.id group by c.id, c.name ORDER BY c.id;