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
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) = 1but 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.
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) = 1by itself and it only returned me one row instead of returning me one row for each ID so my assumption was right
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.IDOops, i didn't notice the missing PARTITION, i simply cut & pasted :-)
Dieter
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 = 1However 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?