CODEWITHSUNDEEP
sqlpostgresqljoinsubquery
user3642271
user3642271

Reputation: 47

Joining a subquery using an outer column that uses a group function

So this one should be pretty simple for most of you:

My table has an ID, an order_id and a status. The same order_id may have several IDs.

What I need to do is get the last ID from each order_id, which is pretty simple:

SELECT order_id, max(ID) AS last_id 
FROM mytable
GROUP BY order_id

Now, I also need to get the status that is linked to last ID, so what I was trying to do was:

SELECT order_id, max(ID) AS last_id, x.status
FROM mytable t

LEFT JOIN (SELECT ID, status
          FROM mytable) x ON last_id = x.ID

I know I'm not allowed to use the last_id alias to join the subquery, as it says it does not exist. So how do I go about this?

Upvotes: 0

Views: 42

Answers (2)

Jim Jones
Jim Jones

Reputation: 19623

An alternative is to DISTINCT ON the column order_id and then apply max() on id.

SELECT DISTINCT ON (order_id) 
  order_id, max(id) AS last_id,
  status
FROM mytable 
GROUP BY order_id,status;

Demo: db<>fiddle

Upvotes: 0

JMSilla
JMSilla

Reputation: 1449

You can't use the alias in the FROM or in the WHERE parts of the query, you should use max(t.ID):

SELECT order_id, max(t.ID) AS last_id, x.status
FROM mytable t

LEFT JOIN (SELECT ID, status
          FROM mytable) x ON MAX(t.ID) = x.ID

You can also wrap the query as a subquery and then do the join using the alias:

SELECT t.order_id, t.last_id, x.status
FROM (
  SELECT order_id, max(ID) AS last_id
  FROM mytable
) t
LEFT JOIN mytable x
ON t.last_id = x.ID

Upvotes: 1

Related Questions