How to fetch one row inside a group in mysql according to a criteria in MySQL

user_11170382 0
Viewed 52

I have a table like this with 8 rows

+----+------+------+--------+
| id | type | attr1 | attr2 |
+----+------+-------+-------+
|  1 |    a |  abcd |  qwer |
|  2 |    a |  efgh |  tyui |
|  2 |    b |  ijkl |  opas |
|  3 |    a |  mnop |  dfgh |
|  4 |    a |  qrst |  jklz |
|  5 |    a |  uvwx |  xcvb |
|  5 |    b |  yzab |  nmqw |
|  6 |    b |  cdef |  erty |
+----+------+-------+-------+

It is known than type can be either 'a' or 'b'.

I need to select the rows in such a way that if there are more than one rows with same id, then select the one with type 'a'. Else select the row with whatever type is present.

So my desired result should be like

+----+------+------+--------+
| id | type | attr1 | attr2 |
+----+------+-------+-------+
|  1 |    a |  abcd |  qwer |
|  2 |    a |  efgh |  tyui |
|  3 |    a |  mnop |  dfgh |
|  4 |    a |  qrst |  jklz |
|  5 |    a |  uvwx |  xcvb |
|  6 |    b |  cdef |  erty |
+----+------+-------+-------+

I have a MySQL query

SELECT t.id,      
    CASE
        WHEN count(t.id) > 1 THEN 'a'
        ELSE t.type
    END `type`
FROM table1 t
GROUP BY  t.id
ORDER BY  t.type ASC

which gives this result

+----+------+
| id | type |
+----+------+
|  1 |    a |
|  2 |    a |
|  3 |    a |
|  4 |    a |
|  5 |    a |
|  6 |    b |
+----+------+

But I need the respective row with all columns. How to do that?

Note that the MySQL version that I have is 5.7.12.

count mysql sql
3 Answers

You don't mention if multiple a's with the same id are possible or what to do in that case. I'm going to assume you want all a rows included. To do that, you just need to exclude b rows when there is a corresponding a row:

select t.*
from table1 t
left join table1 t2 on t2.id=t.id and t.type='b' and t2.type='a'
where t2.id is null;
ysth_173890

you also can do it using window function:

select * from 
 ( 
  select * , row_number() over (partition by id order by case when type = 'a' then 0 else 1 end) rn
 ) t
where rn = 1;
user_13674540

Hmmm . . . I would be inclined to use not exists:

select t.*
from t
where t.type = 'a' or
      not exists (select 1
                  from t t2
                  where t2.id = t.id and t2.type = 'a'
                 );
user_11440350
Related