Mysql Question posted in
The official MySQL documentation can be found here.

Mysql Query batched result grouped

I wish to achieve something like this from a MYSQL SELECT query

I wish to group the results from the select in 500 rows (per batch) and have a column called group (or anything) for the key to the group (eg bartch 0 1 2 3)

SELECT * TABEL WHERE X

Would I do the select into a temp table with the a column-indexed then some how do a limit of 500 and move forward?

The reason behind this is so I can call the number again to get all the same rows again.

Or would it be a better idea to just to select everything thing and let the PHP or Python do the work?

+----+------------+
| Group | Name    
+------------------
|   1   |  Ham
|   1   |  Bread
+------------------
|   2   |  Tomato
|   2   |  Eges

SELECT * FROM TABLE
Tags:

2

Answers


  1. 0

    The idea is to first calculate the data belonging to the same group and then group by the group number. Since I don’t know what will be done after grouping, I will use concatenating the grouped strings as an example.Using ROW_NUMBER() generates a row number, and by subtracting 1 and dividing by the number of rows per group, we calculate the group number, applying the FLOOR() function to round down in the outer layer.Because x/x = 1, if the row number starts from 1, rounding down will result in the number of groups being one less than needed (for example: 1 to (x-1) will be batch 1, and x to (2x-1) will be batch 2…).Therefore, we need to subtract "1 from the row number" to achieve the effect of grouping as 1 to x.

    -- The group number corresponding to each row of data is calculated
with batchMap as (
    select
        floor((row_number() over () -1) / 500) + 1 batch,
        name
    from t
)
-- Group data with the same group number
select
    batch,
    group_concat(name,',')
from batchMap
group by batch
order by batch
    Login or Signup to reply.
  2. 0

    The main issue here could be this:

    The reason behind this is so I can call the number again to get all the same rows again.

    To be sure to fetch the same rows of groups (0, 1, 2, …) it is essencial to have the same and consistent order by to define your groups. It is not clear from the question do you have such a column(s) to do the ordering consistently.

    1. table data with ID column to be used in Order By (consistently)

    --    S a m p l e    D a t a :
Create Table tbl_with_id ( id int, food text );
Insert Into tbl_with_id VALUES
  ( 1, 'Ham'),
  ( 2, 'Bread'),
  ( 3, 'Onion'),
  ( 4, 'Garlic'),
  ( 5, 'Potato'),
  ( 6, 'Salad'),
  ( 7, 'Rice');
Select * From tbl_with_id;
    id food
    1 Ham
    2 Bread
    3 Onion
    4 Garlic
    5 Potato
    6 Salad
    7 Rice 
    --      S Q L :    (group/batch of 3 rows)
Select    id, food, 
          FLOOR( ( Row_Number() Over(Order By id) - 1 ) / 3) as grp
From      tbl_with_id
    id food grp
    1 Ham 0
    2 Bread 0
    3 Onion 0
    4 Garlic 1
    5 Potato 1
    6 Salad 1
    7 Rice 2

    … if you add a new row with a new id …

    Insert Into tbl_with_id VALUES ( 8, 'Beans' );

    … and run the SQL again the new row (8, ‘Beans’) will be added at the end, but all previous group data willl remain the same …

    id food grp
    1 Ham 0
    2 Bread 0
    3 Onion 0
    4 Garlic 1
    5 Potato 1
    6 Salad 1
    7 Rice 2
    8 Beans 2

    2. table data without a column to be used in Order By (consistently)

    S a m p l e D a t a :

    food
    Ham
    Bread
    Onion
    Garlic
    Potato
    Salad
    Rice

    … if you don’t order your data there could be different arbitrary ordering whenever you run the code … so lets order it by food

    --      S Q L :    (group/batch of 3 rows)
Select    food, 
          FLOOR( ( Row_Number() Over(Order By food) - 1 ) / 3) as grp
From      tbl_no_id

    R e s u l t :

    food grp
    Bread 0
    Garlic 0
    Ham 0
    Onion 1
    Potato 1
    Rice 1
    Salad 2

    … now if you add a new row with a new food …

    Insert Into tbl_with_id VALUES ( 'Beans' );

    … and run the SQL again the new row (‘Beans’) will change the group content where it was inserted and all following groups content …

    food grp
    Beans 0
    Bread 0
    Garlic 0
    Ham 1
    Onion 1
    Potato 1
    Rice 2
    Salad 2

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search