My desired output is something like:
My attempt is as follows:
SELECT id, g_minute, COUNT(DISTINCT id) OVER(ORDER BY f_datetime DESC) AS counter FROM tbl_X
When I try this I get:
This version of MariaDB doesn't yet support 'COUNT(DISTINCT) aggregate as window function' */
Is there another 'simple' way of doing this? I want a cumulative counter of unique id's. Is there another window
function that can do the same thing?
CodePudding user response:
Try a nested window function instead:
SELECT id
,g_minute
,sum(seqno = 1) OVER (
ORDER BY f_datetime DESC
) AS counter
FROM (
SELECT t.*
,row_number() OVER (
PARTITION BY id ORDER BY f_datetime DESC
) AS seqno
FROM tbl_X t
) r
CodePudding user response:
This is the way I get the count of values when I'm testing
SELECT
ID,
G_MINUTE,
COUNT(ID)
FROM tbl_X
GROUP BY ID, G_MINUTE
This will give you the distinct count of the ID and g_minute