mysql - group by part of the rows

Question

Using mysql, Suppose my table looks like this:

| id | name   | type  
| 1  | name1  | type 1
| 2  | name2  | type 2
| 3  | name3  | type 2
| 4  | name4  | type 3
| 5  | name4  | type 4
| 6  | name5  | type 2
| 7  | name6  | type 3

I'd like to get all the rows from the table and group all of type 2 into a single row.
In this example it doesn't matter if the row is id 2 or 3.

The result should look like this:

    | id | name   | type  
    | 1  | name1  | type 1
    | 3  | name3  | type 2
    | 4  | name4  | type 3
    | 5  | name4  | type 4
    | 7  | name6  | type 3

You can't.... If you group rows in `type` column, you won't be able to see ALL the other rows from `id` and/or `name` if they have the same value AND you won't have a grouped `type` if they have different column... Anyways the query for grouping would be : `select id,name,type from your_table group by type ;` — Jason Krs, Aug 06 '17 at 10:28
Hmmm @JasonKrs's comment just appeared. But indeed, like he said, just `group by type`. Might also have a look at [this answer](https://stackoverflow.com/questions/5967130/mysql-select-one-column-distinct-with-corresponding-other-columns) and/or the [`DISTINCT` reference](https://dev.mysql.com/doc/refman/5.7/en/distinct-optimization.html) - might apply to what you're trying to do, not sure ;) — rkeet, Aug 06 '17 at 10:33
If I group by type, I won't get id 4 or 5. I want to get all the row and group only type 2. — Sharon Haim Pour, Aug 06 '17 at 10:34
This sounds like you are trying to solve a (different) problem, but asking about how to execute the solution you are thinking of, instead of asking how to solve the original problem in a maybe different way (an [xy-problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem)). — Solarflare, Aug 06 '17 at 10:41
@Solarflare Looking at the answer I posted and your comment I think I actually (eventually lol) understood his request — Jason Krs, Aug 06 '17 at 10:43
@Jason yes, his request is probably what you understood, but I am not sure if it is a good request in the sense that it solves his underlying problem (I assume he wants to hide parts of the data, like the "+" in programming ides to hide a procedure or a loop and just show the header), so he might want to add his underlying problem. — Solarflare, Aug 06 '17 at 10:56

O. Jones · Answer 1 · 2017-08-06T12:32:33.403

I guess you want a result set with just one row for each value of type. Your sample table shows three different rows with a type of type 2, and one each of several other type values.

This query returns one row per type showing the row with the largest id value for each type. If id is an autoincrementing primary key, this means it shows the most recently inserted row with that type. (http://sqlfiddle.com/#!9/1f8c54/3/0)

SELECT a.id, a.name, a.type
  FROM tbl a
  JOIN (
              SELECT MAX(id) id
                FROM tbl
               GROUP BY type
       ) m ON a.id = m.id
 GROUP BY type

You can change the subquery (SELECT MAX(id)...) to choose an appropriate row for your application. For example MIN(id) gives the least recently inserted row.

If you only want to do that data-reduction operation for one value of type, you'll need a UNION to recover the detail rows from the other type values. (http://sqlfiddle.com/#!9/1f8c54/8/0)

SELECT * 
 FROM (
    SELECT a.id, a.name, a.type
      FROM tbl a
      JOIN (
                  SELECT MAX(id) id
                    FROM tbl
                   GROUP BY type
           ) m ON a.id = m.id
     WHERE type = 'type 2'
     GROUP BY type
     UNION
    SELECT id, name, type
      FROM tbl
     WHERE type <> 'type 2'
   ) q
ORDER BY type, id

mysql - group by part of the rows

1 Answers1