Hi guys, I've got the following query:
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... Thanks Patrick