MySQL query for selecting multiple rows as arrays of those rows data

Question

Is there a way to select frist 3 rows and after that next 3 ( offset 3 ) and get the result as two arrays in a single query ? Something like:

(SELECT * FROM product WHERE 1 LIMIT 3) as first_array
(SELECT * FROM product WHERE 1 OFFSET 3 LIMIT 3) as second_array

Hope you understand me. Sorry about the explanation just dont't know how to explain in other way.

Lets say I only want the ids - output example:

id_1   id_2
 1       4
 2       5
 3       6

What I have tried from the answers below the post is :

SELECT id as id_1 FROM `ct_product` WHERE 1 LIMIT 3
UNION ALL
SELECT id as id_2 FROM `ct_product` WHERE 1 LIMIT 3 OFFSET 3

The result is strange for me. It seems it returns only the second query results and they are not the 4th 5th and 6th row but the 5th 6th and 3th (in this order). My table rows are:

What do you mean by "arrays"? MySQL doesn't have array variables. — Ildar Akhmetov
– Ildar Akhmetov, Commented Aug 16, 2018 at 5:50
Just to group it. Gave it alias for more than one result - result_1 ( id, name) / result_2 (id, name) where the result_1 have to hold the data of the first group of products anb result_2 - the second group of products. — Toma Tomov
– Toma Tomov, Commented Aug 16, 2018 at 5:58
as i getting your requirement you can use union to combine your both condition in a single query — Ankit Agrawal
– Ankit Agrawal, Commented Aug 16, 2018 at 6:04
Please give an example of how your results output should look like. — Roshana Pitigala
– Roshana Pitigala, Commented Aug 16, 2018 at 6:16

Nick · Accepted Answer · 2018-08-16 06:57:55Z

1

You could do it with this query:

SELECT a1.id, a2.id
FROM (SELECT *, @rownum1:=@rownum1+1 AS rownum
      FROM (SELECT id
            FROM `ct_product`
            LIMIT 3
            ) art
      JOIN (SELECT @rownum1 := 0) r
      ) a1
JOIN  (SELECT *, @rownum2:=@rownum2+1 AS rownum
      FROM (SELECT id
            FROM `ct_product`
            LIMIT 3, 3
            ) art
      JOIN (SELECT @rownum2 := 0) r
      ) a2
ON a1.rownum = a2.rownum

Output:

This query works by creating two new tables with artificially generated row numbers (@rownum1 and @rownum2) from the first 3 and the second 3 rows in the original table. They are then JOINed on matching row numbers to get the desired result.

answered Aug 16, 2018 at 6:57

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL query for selecting multiple rows as arrays of those rows data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related