Tom Tom - 2 months ago 9
SQL Question

Invalid column name using row_number() OVER(PARTITION BY )

I have two tables

TBL_CONTACT
and
TBL_PHONE
that I am trying to join.

The
TBL_PHONE
table contains duplicate rows for contacts with multiple phone numbers, so I am partitioning these and trying to select the first instance only.

Here is the code I have:

SELECT
(CONTACT.CONTACTID),
(CONTACT.FULLNAME),
(PHONE.contactid),
(PHONE.numberdisplay),
(row_number() OVER(PARTITION BY PHONE.Contactid ORDER BY PHONE.Contactid)) prn
FROM
"ACT2015Demo"."dbo"."TBL_CONTACT" AS CONTACT
INNER JOIN
TBL_PHONE AS PHONE
ON CONTACT.Contactid = PHONE.Contactid
WHERE
CAST(Editdate AS DATE) = CAST(GETDATE() AS DATE)


This gives the following:

CONTACTID FULLNAME CONTACTID NUMBERDISPLAY PRN
1001 Name1 1001 Tel1001 1 1
1001 Name1 1001 Tel1001 2 2
1002 Name2 1002 Tel1002 1 1
1003 Name3 1003 Tel1003 1 1
1003 Name3 1003 Tel1003 2 2
1003 Name3 1003 Tel1003 3 3


I then want to use the PRN column to limit the output to only rows with PRN = 1. I have tried the following, as this has worked for me in the past on less complex joins:

SELECT
(CONTACT.CONTACTID),
(CONTACT.FULLNAME),
(PHONE.contactid),
(PHONE.numberdisplay),
(row_number() OVER(PARTITION BY PHONE.Contactid ORDER BY PHONE.Contactid)) prn
FROM
"ACT2015Demo"."dbo"."TBL_CONTACT" AS CONTACT
INNER JOIN
TBL_PHONE AS PHONE
ON CONTACT.Contactid = PHONE.Contactid AND PRN = 1
WHERE
CAST(Editdate AS DATE) = CAST(GETDATE() AS DATE)


However, this gives me the invalid column name error for
PRN
? I have also tried using
PRN = 1
as part of the
WHERE
with the same error.

How can I get
PRN
to work as a column name and limit the output?

Answer

I have tried the following, as this has worked for me in the past on less complex joins...The way you tried will never work for simple joins as well.You have to use Derived table or CTE

Select 
*
from
(
SELECT
    (CONTACT.CONTACTID) as concontactid,
    (CONTACT.FULLNAME),
    (PHONE.contactid),
    (PHONE.numberdisplay),
    (row_number() OVER(PARTITION BY PHONE.Contactid ORDER BY PHONE.Contactid)) prn
FROM
    "ACT2015Demo"."dbo"."TBL_CONTACT" AS CONTACT
INNER JOIN
    TBL_PHONE AS PHONE
    ON CONTACT.Contactid = PHONE.Contactid 
WHERE
    CAST(Editdate AS DATE) = CAST(GETDATE() AS DATE)
) as b
where prn=1