SQL Server SQL Multipart identifier error when attempting to filter using a field from a subquery [duplicate]

falq053o 于 2023-03-17 发布在其他

关注(0)|答案(1)|浏览(169)

This question already has answers here:

Get top 1 row of each group (19 answers)
Closed yesterday.

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?

sql-server

来源：https://stackoverflow.com/questions/75743749/sql-multipart-identifier-error-when-attempting-to-filter-using-a-field-from-a-su

1条答案

按热度按时间

0yg35tkg1#

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

赞(0）回复(0）举报 2023-03-17

我来回答

SQL Server SQL Multipart identifier error when attempting to filter using a field from a subquery [duplicate]

1条答案

相关问题

热门标签

最新问答