select distinct postgres 9.2

2016-09-19 9:18 GMT+12:00 Patrick B <patrickbakerbr@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!