title: Selecting distinct column of a distinct column using SQL Server
body: <p>I am attempting to select just 2 distinct column to determine the records that are shown in my query. The column userid is capable of owning several houses which mean userid can be present multiple times currently. However, I only care if they specific colors of a house, so I'd like the userid column to be distinct, along with the House colum while the rest of the rows can remain to be whatever is within that row.</p>
  
  <pre><code>Select UserID, House, NumOfPpl, NumOfCars 
  from people
  </code></pre>
  
  <p>Results:</p>
  
  <pre><code>userID  House  NumOfPpl  NumOfCars 
  -----------------------------------
  1a        red    3          2
  1a        blue   1          1
  1a        red    5          4
  1a        green  2          3
  1a        blue   1          3
  2a        red    3          3
  3a        green  4          6
  3ab       red    2          1
  3ab       red    5          5
  3ab       blue   2          1
  </code></pre>
  
  <p>Would need to be:</p>
  
  <pre><code>userID  House  NumOfPpl  NumOfCars
  ----------------------------------
  1a        red    3          2
  1a        blue   1          1
  1a        green  2          3
  2a        red    3          3
  3a        green  4          6
  3ab       red    2          1
  3ab       blue   2          1
  </code></pre>
  
  <p>I have used cte to get rid of duplicate userid's, but how can I get rid of duplicate house's, within userid's?</p>
  
  <pre><code> ;with cte AS (
      select userid, 
      house, 
      numofppl, 
      numofcars, 
      row_number() OVER(partition by userID order by house) AS rowcounter
      FROM people
  )
  
  SELECT userid, house, numofppl, numofcars
  from cte 
  WHERE rowcounter = 1
  </code></pre>