Create tablegroup with 4 different values ordered by other value by select in mariadb

from a table i want to create groups by 4 different "Klasse" ordered by KW in one select query.

Each group of 4 must NOT contain same "Klasse" and the next group have to be the next highest KW.

output wanted:

so grp1 contains "Klasse" = 1 "Kreuzritter", 1 "Zauberer", 1 "Dämonenjäger", 1 "Mönch" with the highest KW

grp2: contains "Klasse" = 1 "Dämonenjäger", 1 "Zauberer", 1 "Kreuzritter", 1 "Barbar" (2nd best KW constalation)

grpX can have any other constalation by "Klasse" but the next highest KW group

fiddle here: http://sqlfiddle.com/#!9/fc2f87/1/0

it may happen that i further need some more WHEREs and excludes "Klasse" to make this table come to my wishes

Thank you very kindly

I think your problem could not be solved by SQL and should be implemented it in back-end code. Here is the closed query that could group your data into group of 4 Klasse.

-- 3. start part 3
SELECT CONCAT('group', FLOOR(row_number/4) + 1) AS Groupname, GROUP_CONCAT(Name) AS Players
FROM
  -- 2. start part 2
  (SELECT part1.*, (@row_number := @row_number + 1) AS row_number
  FROM
    -- 1. start part 1
    (SELECT 
         Klasse, Name, KW,
         (@class_rank := CASE WHEN @klasse = Klasse THEN @class_rank + 1 ELSE 0 END) AS class_rank,
         @klasse := Klasse
     FROM valar_date
         CROSS JOIN (SELECT @class_rank := -1, @klasse := '') param1
     ORDER BY Klasse, KW+0 DESC) part1
     -- 1. end part 1
     CROSS JOIN (SELECT @row_number := -1) param2
  ORDER BY part1.class_rank, part1.KW DESC) part2
  -- 2. end part 2
GROUP BY FLOOR(row_number/4);
-- 3. end part 3

Explanation of the above query:

1. Part 1: ranking data inside each Klasse by value of KW column.
2. Part 2: ordering data by ranking value of step 1, then assign row number for each record. By doing this, data will be ordered from the highest ranking to the lowest ranking of each group.
3. Part 3: grouping each 4 records into 1 group.

See demo here