SQL subquery questions, "ERROR: invalid reference to FROM-clause entry ..."
Having trouble with SQ开发者_Python百科L (currently using postgresql)
I have this query as I need to compare the most recent item and the second most recent item:
SELECT p1.*, p2.price_cents FROM "prices" p1
INNER JOIN
(
SELECT price_cents, game_id from prices as p WHERE p.game_id = p1.game_id
ORDER BY p.created_at DESC LIMIT 1 OFFSET 1
)
p2 ON p2.game_id = p1.game_id
This produces a few errors:
ERROR: invalid reference to FROM-clause entry for table "p1"
LINE 1: ...AND p.game_id = p1.game_id...
^
HINT: There is an entry for table "p1", but it cannot be referenced from this part of the query.
Is there any reason I can't access p1 from that subselect, is it a existence issue, as in, p1's data isn't available yet? Is there another way to do this with a JOIN?
Try this one
SELECT p1.*, (
SELECT price_cents
FROM "prices" p
WHERE p1.game_id = p.game_id
ORDER BY p.created_at DESC LIMIT 1 OFFSET 1
) as price_cents
FROM "prices" p1
UPDATE according to authors comment
If you need more than one column from second recent entry, you can try following snippet
SELECT * FROM (
SELECT p.*, (
SELECT id
FROM "prices"
WHERE p.game_id = game_id
ORDER BY created_at DESC LIMIT 1 OFFSET 1
) AS second_id
FROM "prices" p
) p1 INNER JOIN "prices" p2 ON p1.second_id = p2.id
You need a LATERAL JOIN:
SELECT p1.*, p2.price_cents FROM "prices" p1
INNER JOIN LATERAL
(
SELECT price_cents, game_id from prices as p WHERE p.game_id = p1.game_id
ORDER BY p.created_at DESC LIMIT 1 OFFSET 1
)
p2 ON p2.game_id = p1.game_id
精彩评论