-1

Sample data:

id type 
1  0
2  1
3  0
4  1
5  0
6  1
7  0
10 1
11 1
12 1
13 1
15 1

Expected result after grouping, if max rows count in group is 3, and we can merge rows with same type in range 2 rows after current:

id type count ids(no need to output it, it's only for understanding)
1  0    3     1,3,5
2  1    3     2,4,6
7  0    1     7
10 1    3     10,11,12
15 1    1     15

Merging adjacent rows is simple done by using variables, but merging rows with same type through another it's hell. How i can do it in MySQL, or this isn't possible?

  • Can I clarify, you want the first id, grouped by type and you want a maximum group size of 3? – mendosi Nov 17 '16 at 3:26
  • Partial yes. But i want group based on row position, if two rows adjacent, and have same type - UNION, or if range current row position - 2 to current row position has element with same type and groups count <= 3 - union with it(or simply not select because we already have min(id)) – user3928409 Nov 18 '16 at 7:44
0

Since you have already figured out how to use variables, I am showing only '...':

( SELECT min(id) AS mid_id, type, ...
    FROM tbl
    WHERE type = 0
    ORDER BY id
)
UNION ALL
( SELECT min(id) AS mid_id, type, ...
    FROM tbl
    WHERE type = 1
    ORDER BY id
)
ORDER BY mid_id;

That should work for a small, fixed, number of types. Else, you need an extra variable, plus rearrange the data before processing it; then rearrange again afterwards:

SELECT  ... -- break on changes in `type` in addition to every 3rd item
    FROM (
        SELECT * FROM tbl
            ORDER BY type, id -- first rearrangement
         )
    ORDER BY min_id  -- second rearrangement

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.