John John - 5 months ago 27
SQL Question

Group by priority

I have this

+---------+--------+-------+
| article | name |status |
+---------+--------+-------+
| 0001 | A | enable|
| 0002 | A | temp |
| 0003 | B | enable|
| 0004 | C | enable|
+---------+--------+-------+


I want to select all from this table 'product' but I want to group by name and if there is a status temp I want to ignore the enable status and display only the product with the temp status

This result after query will be :

+---------+--------+-------+
| article | name |status |
+---------+--------+-------+
| 0002 | A | temp |
| 0003 | B | enable|
| 0004 | C | enable|
+---------+--------+-------+


Could you help me to build this query ?

Answer

Try this, hope help for you;)

SQL Fiddle

MySQL 5.6 Schema:

CREATE TABLE table1
    (`article` int, `name` varchar(1), `status` varchar(6))
;

INSERT INTO table1
    (`article`, `name`, `status`)
VALUES
    (0001, 'A', 'enable'),
    (0002, 'A', 'temp'),
    (0003, 'B', 'enable'),
    (0004, 'C', 'enable')
;

Query 1:

select  t1.*
from table1 t1
inner join (
  select count(distinct status) cnt, name, group_concat(status) as names from table1 group by name
) t2 on t1.name = t2.name
and (t2.cnt = 1 or (find_in_set('temp', names) > 0 and t1.status = 'temp'))
group by t1.name, t1.status

Results:

| article | name | status |
|---------|------|--------|
|       2 |    A |   temp |
|       3 |    B | enable |
|       4 |    C | enable |
Comments