title: how to select max value according to the combination of three columns in SQL
body: <p>I am working on one query and got stuck in to that.</p>
  
  <p>I have a sample data here</p>
  
  <pre><code>Name   Main_Model    Sub_Model     Sub_Sub_Model
  ===============================================
  Ver1    1             0               0
  ver2    1             0               0
  ver1    1             2               0
  ver2    1             1               0
  ver1    2             0               0
  ver2    1             1               1
  ver1    2             2               1
  </code></pre>
  
  <p>with this data I have to select a maximum Model number. So for example</p>
  
  <p>The maximum model number for Version 1 i.e. ver1 is</p>
  
  <pre><code> Name   Main_Model    Sub_Model     Sub_Sub_Model
   ===============================================
   Ver1    2            2               1
  </code></pre>
  
  <p>for version 2 it would be</p>
  
  <pre><code> Name   Main_Model    Sub_Model     Sub_Sub_Model
   ===============================================
   Ver2    1            1               1
  </code></pre>
  
  <p>I have tried inner joins but no luck. I am really confuse about it's logic.
  Please guide so that I can complete this task.</p>
  
  <p>Thank you</p>