Re: select distinct postgres 9.2

2016-09-19 9:18 GMT+12:00 Patrick B <patrickbakerbr(at)gmail(dot)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!