Teradata Rank Over Query (Getting one row to left join)

Database
Enthusiast

Teradata Rank Over Query (Getting one row to left join)

Hi am new to Teradata and am stuck with a problem

There is an ID table which stores an Unique ID given to each person

 
CREATE TABLE IDS(
ID VARCHAR(8),
UPDATED_DATE DATE)


Then we have a name and address table which do not have any primary keys that stores demographic information for the IDS

    CREATE TABLE NAMES(
ID VARCHAR(8),
NAME VARCHAR(50))
CREATE TABLE ADRRESSES(
ID VARCHAR(8)
ADDRESS VARCHAR(200))


Now each ID can have multiple name and IDS. However for names and address I want to use the ones that are have more counts. If two names have the same COUNT I just want the First row

ID            NAME            COUNT

1234         John Smith       6

1234        Johnnie Smith   6

1234         J Smith            2

In the above example I want the name John Smith. Here is the left Join I am performing since an ID may not have a name or address. Here is what I am trying

   
SELECT * FROM
(SELECT ID as V_ID from IDS) a
LEFT JOIN
(SELECT ID, NAME, COUNT(*) AS COUNTER,(RANK() OVER(ORDER BY COUNTER DESC)) AS RNK
FROM NAMES
GROUP BY ID)b
ON a.ID = b.ID
AND b.RNK = 1 -- Should give me only the first row
LEFT JOIN
(SELECT ID, ADDRESS, COUNT(*) AS COUNTER, (RANK() OVER (ORDER BY COUNTER DESC) ) AS RNK
FROM ADDRESSES
GROUP BY ID) c
ON c.ID = a.ID
And c.RNK = 1


However this is not getting me the desired result. I tried using ROW NUMBER instead of RANK also but still no results. How should I write this query in TERDATA?

5 REPLIES
Junior Contributor

Re: Teradata Rank Over Query (Getting one row to left join)

RNK is part of the Join condition -> no WHERE-condition -> Outer Joins will return all rows.

You must filter in your Derived Table:

SELECT * FROM
(SELECT ID as V_ID from IDS) a
LEFT JOIN
(SELECT ID, NAME, COUNT(*) AS COUNTER
FROM NAMES
GROUP BY ID
qualify ROW_NUMBER() OVER(ORDER BY COUNTER DESC) = 1
)b
ON a.ID = b.ID
LEFT JOIN
(SELECT ID, ADDRESS, COUNT(*) AS COUNTER
FROM ADDRESSES
GROUP BY ID
qualify ROW_NUMBER() OVER(ORDER BY COUNTER DESC) = 1
) c
ON c.ID = a.ID

Dieter

Enthusiast

Re: Teradata Rank Over Query (Getting one row to left join)

Dieter I actually tried your query before having the qualify inside the subquery and it doesnt work and gives me all nulls even when there is an address present. So I tried qualify outside the subquery like 

SELECT * FROM
(SELECT ID as V_ID from IDS) a
LEFT JOIN
(SELECT ID, NAME, COUNT(*) AS COUNTER
FROM NAMES
GROUP BY ID

)b
ON a.ID = b.ID
qualify ROW_NUMBER() OVER(ORDER BY b.COUNTER DESC) = 1
LEFT JOIN
(SELECT ID, ADDRESS, COUNT(*) AS COUNTER
FROM ADDRESSES
GROUP BY ID
qualify ROW_NUMBER() OVER(ORDER BY b.COUNTER DESC) = 1
) c
ON c.ID = a.ID
qualify ROW_NUMBER() OVER(ORDER BY c.COUNTER DESC) = 1

but this was an invalid query. I am not sure why your query gave me only nulls. Is it because its only fetching 1 row inside the subquery and then failing to join it to the manifest_ids from the IDS table since it only returned 1 row from the inner query. Though the group by IDs should have returned me all the IDS so am unsure of whats going on.

Enthusiast

Re: Teradata Rank Over Query (Getting one row to left join)

as an addendum I just ran 

SELECT ID, ADDRESS, COUNT(*) AS COUNTER
FROM ADDRESSES
GROUP BY ID
qualify ROW_NUMBER() OVER(ORDER BY b.COUNTER DESC) = 1

by itself and it only returned me one row instead of returning me one row for each ID so my assumption was right

Enthusiast

Re: Teradata Rank Over Query (Getting one row to left join)

dieter I solved it .. what it needed was a partition by ID clause

SELECT * FROM
(SELECT ID as V_ID from IDS) a
LEFT JOIN
(SELECT ID, NAME, COUNT(*) AS COUNTER
FROM NAMES
GROUP BY ID
qualify ROW_NUMBER() OVER(PARTITION BY ID ORDER BY COUNTER DESC) = 1
)b
ON a.ID = b.ID
LEFT JOIN
(SELECT ID, ADDRESS, COUNT(*) AS COUNTER
FROM ADDRESSES
GROUP BY ID
qualify ROW_NUMBER() OVER(PARTITION BY ORDER BY COUNTER DESC) = 1
) c
ON c.ID = a.ID

Junior Contributor

Re: Teradata Rank Over Query (Getting one row to left join)

Oops, i didn't notice the missing PARTITION, i simply cut & pasted :-)

Dieter