WITH accounts AS ( SELECT c.id AS company_id, c.name_first AS c_first_name, c.name_last AS c_last_name, c.company AS c_name, FROM public.clients c WHERE id = 33412393 ORDER BY 1 LIMIT 100 ) SELECT r.parts[4]::INT AS account_id, r.parts[6]::INT AS n_id, r.parts[9] AS variation, size, FROM ( SELECT string_to_array(full_path, '/') AS parts, size FROM public.segments s WHERE public.f_get_account_from_full_path(s.full_path) IN (SELECT company_id FROM accounts) ) r
... and I want to get only the greatest note_id order by size,
How can I put this query into the above one?
SELECT DISTINCT ON (n_id) n_id, MAX(size) FROM test1 GROUP BY note_id, size, st_ino, account_id ORDER BY note_id, size desc
DISTINCT ON (r.parts[6]::INT) AS n_id - it doesn't work...