title: Teradata Rank Over Query (Getting one row to left join)
body: <p>Hi am new to Teradata and am stuck with a problem</p>
  
  <p>There is an ID table which stores an Unique ID given to each person</p>
  
  <pre><code>CREATE TABLE IDS(
  ID VARCHAR(8),
  UPDATED_DATE DATE)
  </code></pre>
  
  <p>Then we have a name and address table which do not have any primary keys that stores demographic information for the IDS</p>
  
  <pre><code>CREATE TABLE NAMES(
  ID VARCHAR(8),
  NAME VARCHAR(50))
  CREATE TABLE ADRRESSES(
  ID VARCHAR(8)
  ADDRESS VARCHAR(200))
  </code></pre>
  
  <p>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</p>
  
  <p>ID              NAME                COUNT</p>
  
  <p>1234    John Smith  6</p>
  
  <p>1234    Johnnie Smith   6</p>
  
  <p>1234    J Smith     2</p>
  
  <p>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</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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?</p>