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'
]

Answer 1

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.