I think I might have figured this out using Postgresql's array_agg
, but with a big downside, so I'd still be interested in seeing other answers.
SELECT
period,
("array_agg"(ROW(motive_value, motive_name, sample_size)))[1:5] AS sample_list
FROM
(
SELECT
period,
motive_value,
COUNT(motive_value) AS sample_size,
motives."name" AS motive_name
FROM surveys
INNER JOIN motives ON surveys."motive_value" = motives."value"
WHERE var BETWEEN 0 AND 10
GROUP BY period, motive_value, motive_name
ORDER BY sample_size DESC
) AS row
GROUP BY period
ORDER BY period DESC
LIMIT 6
The only down side is that, instead of columns, I get an array like this:
-----------------------------------------------------------------------------
| period | sample_list |
-----------------------------------------------------------------------------
| 2 | {"(1,\"Blocked\",1)", "(2,\"Delivery\",1)", ... } |
| 1 | {"(1,\"Blocked\",1)", "(3,\"Forgotten\",1)", "(5,\"Other\",1)" } |
-----------------------------------------------------------------------------
Best Answer
You can get some information about the last change to a table with
xmin
, eg:But you need to be aware of many caveats including modulo and wraparound and frozen xids.
testbed:
method:
cleanup: