SQL subquery questions, "ERROR: invalid reference to FROM-clause entry ..."

Question

Having trouble with SQL (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?

Just a side note: you don't need the quotes around `prices` – Sep 06 '11 at 17:51 — , Sep 06 '11 at 17:51

score 3 · Accepted Answer · edited May 03 '17 at 20:21

3

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

edited May 03 '17 at 20:21

fbernier

12,278
2
24
30

answered Sep 06 '11 at 16:49

hazzik

13,019
9
47
86

This will work, but what if I needed more than one column from the subselect? I'd need to use another subselect. Performance-wise it was why I was doing trying to do a JOIN. – scottkf Sep 06 '11 at 16:55
are there any `id` column in `prices` table? – hazzik Sep 06 '11 at 17:10
I don't understand what the mistake in the OPs query was, could you elaborate? – PeerBr Dec 21 '11 at 23:16
2

@PeerBr OP tried to access p1 from within the JOIN statement which doesn't have access to the other tables it's being joined to. – sfbahr Apr 01 '16 at 07:02

score 2 · Answer 2 · answered Jul 22 '21 at 03:02

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

SQL subquery questions, "ERROR: invalid reference to FROM-clause entry ..."

2 Answers2

Linked