CODEWITHSUNDEEP
mysql
user3712320
user3712320

Reputation: 116

Mysql - group by with condition if field match choose on base of other field

My Table is 

id name active gid
38  abc  1      2
49  cba  1      1
51  dfg  1      1
23  gst  1      2
54  dfg  1      2
58  gst  1      1

in a simple case i used this query

SELECT  id, name FROM test_table WHERE (gid = 2 OR gid = 1) and active = 1 group by name

But my requirement is if the table holds several records with same name, the record with gid = 2 should be in result.

if there is more than 1 record with the same name and gid=2, any random record is okay. Same thing if there is only records with gid=1 for a same name.

 id     name    
 38     abc
 49     cba
 23     gst
 54     dfg

Upvotes: 0

Views: 119

Answers (1)

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

SQL Fiddle

MySQL 5.5 Schema Setup:

CREATE TABLE test_table
    (`id` int, `name` varchar(3), `active` int, `gid` int)
;

INSERT INTO test_table
    (`id`, `name`, `active`, `gid`)
VALUES
    (38, 'abc', 1, 2),
    (49, 'cba', 1, 1),
    (51, 'dfg', 1, 1),
    (23, 'gst', 1, 2),
    (47, 'gst', 1, 2),
    (54, 'dfg', 1, 2),
    (58, 'gst', 1, 1)
;

Query 1:

SELECT min(id), t1.name
FROM test_table t1
INNER JOIN
    (SELECT  name, max(gid) as gid
     FROM test_table 
     WHERE gid in (1,2) and active = 1 
     group by name) t2 ON t1.name = t2.name AND 
                          t1.gid = t2.gid
group by t1.name

Results:

| min(id) | name |
|---------|------|
|      38 |  abc |
|      49 |  cba |
|      54 |  dfg |
|      23 |  gst |

Upvotes: 1

Related Questions