SQL Multipart identifier error when attempting to filter using a field from a subquery

Question

Ok, I'm not very good with SQL, so I don't know how to solve this one. I have this query:

DECLARE @supplyPointIds TABLE
                        (
                            id UNIQUEIDENTIFIER
                        )
INSERT INTO @supplyPointIds (id)
VALUES ('1DC9A405-4EC5-4379-BB2C-110973A9936B'),
       ('65745684-7D00-4D8A-9735-2C5BA29852B0')
SELECT R.Id AS LastReadingId, R.SupplyPointInstallationId
FROM Reading R
WHERE R.SupplyPointInstallationId IN (SELECT id FROM @supplyPointIds)
ORDER BY R.SupplyPointInstallationId, R.LastDate DESC

And it produces this:

But what I want to get is this only:

So, I wrote this query:

DECLARE @supplyPointIds TABLE
                        (
                            id UNIQUEIDENTIFIER
                        )
INSERT INTO @supplyPointIds (id)
VALUES ('1DC9A405-4EC5-4379-BB2C-110973A9936B'),
       ('65745684-7D00-4D8A-9735-2C5BA29852B0')
SELECT ReadingId.Id AS LastReadingId, R.SupplyPointInstallationId
FROM Reading R,
     (SELECT TOP 1 RID.Id
      FROM Reading RID
      WHERE RID.SupplyPointInstallationId = R.SupplyPointInstallationId
      ORDER BY RID.LastDate) AS ReadingId -- Only the most recent reading
WHERE R.SupplyPointInstallationId IN (SELECT id FROM @supplyPointIds)
ORDER BY R.SupplyPointInstallationId DESC

But, unfortunately, I get this error:

The multi-part identifier "R.SupplyPointInstallationId" could not be bound.

How can I do to get only those two records I want?

Your subquery, doesn't have access to outer table `R` – Tushar Mar 15 '23 at 10:59 — Tushar, Mar 15 '23 at 10:59

ahmed · Accepted Answer · 2023-03-15T20:02:47.163

Try to join the two tables and use the row_number function as the following:

SELECT R.Id AS LastReadingId, R.SupplyPointInstallationId
FROM 
  (
    SELECT *, 
      ROW_NUMBER() OVER (PARTITION BY SupplyPointInstallationId ORDER BY LastDate DESC) rn
    FROM Reading
  ) R JOIN @supplyPointIds S
ON S.id = R.SupplyPointInstallationId
WHERE R.rn = 1

See demo

This can be also written as:

SELECT LastReadingId, SupplyPointInstallationId
  From
(
  SELECT R.Id AS LastReadingId, R.SupplyPointInstallationId,
       ROW_NUMBER() OVER (PARTITION BY R.SupplyPointInstallationId ORDER BY R.LastDate DESC) rn
  FROM Reading R JOIN @supplyPointIds S
  ON S.id = R.SupplyPointInstallationId
) t
WHERE rn = 1

Although I think the compiler is capable of pushing it through automatically, makes sense to also push the join into the subquery, probably better performance. — Charlieface, Mar 15 '23 at 14:42

SQL Multipart identifier error when attempting to filter using a field from a subquery

1 Answers1