我有一个查询，返回一个ID数组，然后使用自定义函数按如下顺序对其进行排序：
SELECT array_sort(my_array) as sort FROM table
这将返回：
{19,21,24,48}
{19,21,24}
{19,21}
{19}
{16,12,13}
{16,12}
...
我想选择最长的数组并包含不同的第一个元素，因此从上面的列表中我将得到：
{19,21,24,48} and {16,12,13}
我如何实现这一点，我尝试将第一个元素作为单独的项拉出，按长度排序并尝试按以下方式分组：
SELECT DISTINCT (array_sort(path))[1] as first, array_length(path,1) as plen, array_sort(path) as members FROM table GROUP BY first,plen,members ORDER BY plen DESC
这行不通，仅订购列表
使用distinct on条款：
with cte as (
select
array_length(members,1) as plen,
members[1] as first,
members
from (select array_sort(path) as members from table) as a
)
select distinct on (first)
members
from cte
order by first, plen desc
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句