MYSQL: how to return all distinct row in mysql GROUP BY query

Question

I have table T with some column and values

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

and I want to select all distict row with group by clause

my query is

select * 
from T 
group by name 
having max(g2) = 0

my expected result would be

    ========================
    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

** 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

by my MySQL returning only the first row of each group

    ========================
    id |   name |   g1 |  g2 
    =========================
     14 |  abc   |   1  | 0
     42 |  def   |   1  | 0 

ignoring all the differences at other column (id and g1 ).

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.

or is there any setting of mySQL that to be change ??

i already tried SET sql_mode=ONLY_FULL_GROUP_BY, 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.

or, is there any sql_mode setting need to be specified?? or at least why MySQL only return the first row of a group ???

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=''

thank you in advance for any responses...

Answer 1

group by clause is not used for this purpose in any dbms.

select * 
from T 
where g2 =0
order by name

this query should serve your purpose

Here is simple explanation of GroupBy clause. GroupBy clause is usually used with aggregate functions. http://www.w3schools.com/sql/sql_groupby.asp

Answer 2

Try this one -

SELECT t1.* FROM t t1
  LEFT JOIN (
    SELECT name, g1, g2, MIN(id) id FROM t
      GROUP BY name, g1, g2
      HAVING COUNT(*) = 1
    ) t2
  ON t1.name = t2.name AND t1.g1 = t2.g1 AND t1.g2 = t2.g2
WHERE t2.id IS NULL;

This query will return all records duplicates.

My output is:

+------+------+------+------+
| 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 |
+------+------+------+------+