CODEWITHSUNDEEP
mysql
Toma Tomov
Toma Tomov

Reputation: 1694

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

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:

id
1
2
3
4
5
6
7

Upvotes: 0

Views: 243

Answers (1)

Nick
Nick

Reputation: 147216

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:

id  id  
1   4
2   5
3   6

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.

Upvotes: 1

Related Questions