Re: select distinct postgres 9.2

[Patrick B <patrickbakerbr@xxxxxxxxx>]

2016-09-19 9:18 GMT+12:00 Patrick B <patrickbakerbr@xxxxxxxxx>:

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!