title: SQL query Parent-child distinct
body: <p>I have a pair of SQL server tables. </p>
  
  <p>P contains id and name.
  PR contains id, interestrate, tiernumber, fromdate, todate and P.id. PR may contain many rows listed per p.id / tier. (tiers are a list of rates a product may have in any given date period.)</p>
  
  <p>eg: Product 1 tier 1 starts 1/1/2008 to 1/1/2009 and has 6 rates shown 1 row per rate. Product 1 tier 2 starts 1/2/2009 etc etc etc</p>
  
  <p>I need a view on this that shows the P.name and the PR.tiernumber and dates... BUT I want only one row to represent the tier.</p>
  
  <p>This is easy:</p>
  
  <pre><code>SELECT DISTINCT P.ID, P.PRODUCTCODE, P.PRODUCTNAME, PR.TIERNO, 
    PR.FROMDATE, PR.TODATE, PR.PRODUCTID
  FROM dbo.PRODUCTRATE AS PR INNER JOIN dbo.PRODUCT AS P 
    ON P.ID = PR.PRODUCTID
  ORDER BY P.ID DESC
  </code></pre>
  
  <p>This gives me the exact right data... However: this disallows me to see the PR.ID as that would negate the distinct. </p>
  
  <p>I need to limit the resultset because the user needs to just see just a list of tiers, I need to see the PR.ID displaying all of the data.</p>
  
  <p>Any ideas?</p>