2016-09-19 9:18 GMT+12:00 Patrick B <patrickbake...@gmail.com>: > 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 >
Actually.. I was able to get what I needed doing: 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 DISTINCT ON > (r.parts[6]::INT) r.parts[6]::INT AS n_id, > r.parts[4]::INT AS account_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 Thanks guys!
