I need retrive all data of id by rows on MySQL

Question

i have a table(MySQL) with ~35.000.000 rows, and i need retrieve all data in order by id.

Table: data

+--+-----+-----+
|id|field|value|
+--+-----+-----+
|1 |  x  |  10 |
+--+-----+-----+
|1 | y   |  12 |
+--+-----+-----+
|2 | x   |  24 |
+--+-----+-----+
|2 | y   |  25 |
+--+-----+-----+
|3 | z   |  1  |
+--+-----+-----+
|3 | a   |  4  |
+--+-----+-----+
|3 | b   | 7.1 |
+--+-----+-----+

I need retrieve:

+--+--+----+
|10|12|NULL|    <--- ID:1
+--+--+----+
|24|25|NULL|    <--- ID:2
+--+--+----+
|1 | 4|7.1 |    <--- ID:3
+--+--+----+

My Query:

SELECT
(
    SELECT
        value
    FROM
        data
    WHERE
        EXISTS(
            SELECT
                1
            FROM
                members
            WHERE
                data.id = members.id
            LIMIT 1
        )
)

My Result:

#1241 - Operand should contain 1 column(s)

Any idea how to do this, only MySQL and efficiently?

How does your query relate to the desired results? The desired results have three columns but your query only has two. The desired results all seem to be from the `value` column, but your query has `field` and `value`. — Gordon Linoff, Sep 06 '13 at 18:58
I dont belive that it's all - select from select... just like that. — jaczes, Sep 06 '13 at 18:59
What do the three columns in your result set represent? Why does the 'a' value for id 3 go in the same column as the 'y' value for id 1? You need to explain what you are trying to do in a clearer fashion. — Declan_K, Sep 06 '13 at 19:02
This problem doesn't seem to have any logic in it. You're trying to turn one column into three, with a query that produces two, and presents all the data without any sort of context *at all*. What are you trying to accomplish here? — DiMono, Sep 06 '13 at 19:04
sorry, i update the sql query... and i tried with GROUP_CONCAT()... — Olaf Erlandsen, Sep 06 '13 at 19:17
@OlafErlandsenTriskel Do you need GROUP_CONCAT or many columns? — user4035, Sep 06 '13 at 19:19
i need only value column, but all value column in one row by id... — Olaf Erlandsen, Sep 06 '13 at 19:28

peterm · Accepted Answer · 2013-09-06T19:52:17.383

To get your desired output as you stated it in your question (values in separate columns) you can use a query like this

SELECT r.id,
       MAX(CASE WHEN r.rnum = 1 THEN value END) value1,
       MAX(CASE WHEN r.rnum = 2 THEN value END) value2,
       MAX(CASE WHEN r.rnum = 3 THEN value END) value3
  FROM
(
  SELECT id, rnum
    FROM
  (
    SELECT DISTINCT id
      FROM runnerdata
  ) d CROSS JOIN
  (
    SELECT 1 rnum UNION ALL
    SELECT 2 UNION ALL
    SELECT 3
  ) n
) r LEFT JOIN
(
  SELECT id, value, @n := IF(@g = id, @n + 1, 1) rnum, @g := id
    FROM runnerdata
   ORDER BY id, data
) q ON r.id = q.id
   AND r.rnum = q.rnum
 GROUP BY r.id

Output:

| ID | VALUE1 | VALUE2 | VALUE3 |
|----|--------|--------|--------|
|  1 |     10 |     12 | (null) |
|  2 |     24 |     25 | (null) |
|  3 |      4 |    7.1 |      1 |

Note: It won't be blazing fast.

Here is SQLFiddle demo

Now with GROUP_CONCAT() your query might look like

SELECT id, GROUP_CONCAT(value ORDER BY data) data_values
  FROM runnerdata
 GROUP BY id

Output:

| ID |    DATA_VALUES |
|----|----------------|
|  1 |    10.00,12.00 |
|  2 |    24.00,25.00 |
|  3 | 4.00,7.10,1.00 |

Here is SQLFiddle demo

Then in your client code explode values of data_values column while iterating over the resultsest and produce the desired output.

@That's **exactly** what I showed in my second example with `GROUP_CONCAT`. All values are packed as a delimited list in `data_values` column per id. Or did you mean something else? — peterm, Sep 06 '13 at 19:33

score 1 · Answer 2 · answered Sep 06 '13 at 19:36

As you said in the comments:

i need only value column, but all value column in one row by id.

In this case you can use GROUP_CONCAT function. I simplified your case to the minimal working example:

mysql> SELECT * FROM data;
+----+-------+
| id | value |
+----+-------+
|  1 | 1     |
|  1 | 2     |
|  2 | 3     |
|  2 | 4     |
|  2 | 5     |
+----+-------+
5 rows in set (0.00 sec)

SELECT
    data.id,
    GROUP_CONCAT(data.value)
FROM
    data
GROUP BY
    data.id;

gives this:

+----+--------------------------+
| id | GROUP_CONCAT(data.value) |
+----+--------------------------+
|  1 | 1,2                      |
|  2 | 3,4,5                    |
+----+--------------------------+

SQL fiddle temporarily unavailable, so giving you CREATE TABLE statement:

CREATE TABLE `data` (
  `id` int(11) NOT NULL,
  `value` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `data` VALUES (1,'1'), (1, '2'),(2,'3'),(2,'4'),(2,'5');

i need value to convert CSV: "value","values2","value3","value 4\"","value5"\n — Olaf Erlandsen, Sep 06 '13 at 21:39
@OlafErlandsenTriskel If you are using php after receiving the data from SQL, you can convert in to CSV. Or output to CSV file directly from mysql: http://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format — user4035, Sep 07 '13 at 07:55

I need retrive all data of id by rows on MySQL

2 Answers2