SELECT one row per COLUMN value

Question

I have a table like so:

id  |  subscriber_id  |  var_name  |  var_value        | created
1   |  35             |  last_name |  Smith            | [TIMESTAMP]
2   |  35             |  city      |  New York         | [TIMESTAMP]
3   |  35             |  city      |  Boston           | [TIMESTAMP]

In this case, let's say that the row where var_value=Boston has a later timestamp than the var_value=New York row, and thus we will be selecting the Boston row.

I have tried something like this, but it sometimes says something about non-aggregate column grouping.

      SELECT
        var_name, var_value
      FROM
        subscriber_vars
      WHERE
        subscriber = ?
      GROUP BY subscriber
      ORDER BY
        created DESC

So in this case, an expected output would be:

[
    'last_name'  =>  'Smith'
    'city'       =>  'Boston'
]

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Sep 11 '17 at 21:37

Toastrackenigma · Accepted Answer · 2017-09-11T19:25:30.057

3

Using `GROUP BY`

SELECT * FROM (
    SELECT var_name, var_value FROM subscriber_vars ORDER BY created DESC
) x
GROUP BY var_name

GROUP BY will only select the first unique occurrence of a value in the given results set, in the same order as the result rows.

Thus, we run a query to get and sort the information that we want (SELECT var_name, var_value FROM subscriber_vars ORDER BY created DESC), and then run GROUP BY on the results that the query returns.

This now gives us a set of rows, in the order that we want, with the columns we want, which only contains the first occurrences of var_name values.

Now, we just need to SELECT all of those rows back to return them, which is what the SELECT * FROM part is for.

Notes

Check out this StackOverflow post for further reading.

edited Sep 11 '17 at 19:25

answered Sep 11 '17 at 19:15

Toastrackenigma

7,604
4
45
55

Again, I get an error like this: `SELECT list is not in GROUP BY clause and contains nonaggregated column 'x.var_value' which is not functionally dependent on columns in GROUP BY clause` – SISYN Sep 11 '17 at 21:26
Works perfectly fine in test database - which means it is probably a problem with your MySQL configuration. Try following the steps [here](https://stackoverflow.com/a/38002876/). – Toastrackenigma Sep 11 '17 at 21:57
2

if MySQL server SQL mode is configured with `ONLY_FULL_GROUP_BY`, the server will throw an SQL mode error such as `...this is incompatible with sql_mode=only_full_group_by` – Aryo Mar 11 '20 at 06:23

Daniel Burgess · Answer 2 · 2017-09-11T19:26:46.700

If you're fairly limited on the amount of data being stored this way, and the columns are indexed properly, something like the following query should work:

select l.`last_name`, c.`city` from 
(SELECT subscriber_id, var_value as `city` FROM subscriber_vars WHERE var_name='city' ORDER BY created DESC LIMIT 1) c 
join 
(SELECT subscriber_id, var_value as `last_name` FROM subscriber_vars WHERE var_name='last_name' ORDER BY created DESC LIMIT 1) l USING(subscriber_id) 
where c.subscriber_id = 35;

The returned data should look like this:

last_name |city
Smith     |Boston

SELECT one row per COLUMN value

2 Answers2

Using GROUP BY

Notes

Using `GROUP BY`