title: MYSQL: how to return all distinct row in mysql GROUP BY query
body: <p>I have table T with some column and values </p>
  
  <pre><code>T
  ========================
  id |   name |   g1 |  g2 
  =========================
   10 |  abc   |   1  | 1
   14 |  abc   |   1  | 0
   33 |  abc   |   1  | 0
   42 |  def   |   1  | 0 
   52 |  def   |   1  | 1
   63 |  def   |   2  | 0
   66 |  def   |   2  | 0
   67 |  def   |   2  | 0
   74 |  def   |   1  | 0
  </code></pre>
  
  <p>and I want to select all distict row with group by clause</p>
  
  <p>my query is</p>
  
  <pre><code>select * 
  from T 
  group by name 
  having max(g2) = 0
  </code></pre>
  
  <p>my expected result would be</p>
  
  <pre><code>    ========================
      id |   name |   g1 |  g2 
      =========================
       14 |  abc   |   1  | 0
       33 |  abc   |   1  | 0
       42 |  def   |   1  | 0 
       63 |  def   |   2  | 0
       66 |  def   |   2  | 0
       67 |  def   |   2  | 0
       74 |  def   |   1  | 0
  </code></pre>
  
  <p>** i added max(g2) because i need to make add where clause againts each group. :D thank you for the answers. I need the aggregations to limit the return (having clause). another work around is having subquery </p>
  
  <p>by my MySQL returning only the first row of each group</p>
  
  <pre><code>    ========================
      id |   name |   g1 |  g2 
      =========================
       14 |  abc   |   1  | 0
       42 |  def   |   1  | 0 
  </code></pre>
  
  <p>ignoring all the differences at other column (id and g1 ).</p>
  
  <p>as I remember using other DBMS (oracle and MsSQL, or MySQL ), I can expect my query to return expected result above. returning all different row.</p>
  
  <p>or is there any setting of mySQL that to be change ??</p>
  
  <p>i already tried  <code>SET sql_mode=ONLY_FULL_GROUP_BY</code>, but it just show the notification to specify the column, in my understanding not specifying column name in group by clause is to show all row with different column value.</p>
  
  <p>or, is there any sql_mode setting need to be specified?? or at least why MySQL only return the first row of a group ???</p>
  
  <pre><code>set @@global.sql_mode= 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER'
  
  set @@sql_mode=''
  </code></pre>
  
  <p>thank you in advance for any responses...</p>