title: getting the max of each group in a MySql query
body: <p>Whilst I realise this question has been asked in many forms before, sadly the threads that deal with this problem have no helped my situation.</p>
  
  <p>I have a table that looks like this for example:</p>
  
  <pre><code>id|forename|surname|house|gen|academic|sense|dedicated|balance|respect
  -------------------------------------------------------------------------
  1 |dav1    |jone1  |n    |1  |2       |3    |4        |5      |18
  2 |dav2    |jone2  |s    |1  |2       |3    |4        |5      |9
  3 |dav3    |jone3  |w    |1  |2       |3    |4        |5      |10
  4 |dav4    |jone4  |w    |1  |2       |3    |4        |5      |11
  5 |dav5    |jone5  |n    |1  |2       |3    |4        |5      |12
  6 |dav6    |jone6  |h    |1  |2       |3    |4        |5      |13
  7 |dav7    |jone7  |h    |1  |2       |3    |4        |5      |14
  </code></pre>
  
  <p>And the output I would like is:</p>
  
  <pre><code>id|forename|surname|house|total
  -------------------------------
  1 |dav1    |jone1  |n    |33  
  4 |dav4    |jone4  |w    |26  
  2 |dav2    |jone2  |s    |24  
  7 |dav7    |jone7  |h    |29  
  </code></pre>
  
  <p>So it gets the combined total of the six integer columns, then takes the highest from each of the houses and returns the total, the name, the house and the id.</p>
  
  <p>I have done quite a few iterations, and it seems I need to do a nested table (I dont know the exact term for it), but this is what I have been working along the lines of:</p>
  
  <pre><code>SELECT utable.id, utable.forename, utable.surname, utable.house, (utable.gen+utable.academic+utable.sense+utable.dedicated+utable.balance+utable.respect) AS utable.points
  FROM (
      SELECT house, max(`gen`+`academic`+`sense`+`dedicated`+`balance`+`respect`) AS pts FROM users             GROUP BY house
  ) AS ntable
  INNER JOIN users AS utable ON utable.house = ntable.house AND utable.points = ntable.pts
  </code></pre>
  
  <p>However it isnt working, and in particular right now is says the utable.points has an alias that was previously found. I think it has something to do with how I am summing the columns to make the total points but im not sure.</p>
  
  <p>Any help is greatly appreciated :)</p>
  
  <p>(while I know this will get flagged up as a repeat request, I have tried the other threads and a few websites to try and get this to work to no avail. I am testing it in phpMyAdmin server type is MariaDB 5.5.46-MariaDB-1ubuntu0.14.04.2 - (Ubuntu))</p>
  
  <p>Thank you!!!</p>