我在Postgres 11中有下表。
table1
:
id col1 col2 col3 col4
NCT00000374 Drug olanzapine olanzapine olanzapine
NCT00000390 Drug imipramine hydrochloride imipramine hydrochloride imipramine hydrochloride
NCT00000390 Drug imipramine hydrochloride imipramine hydrochloride imipramine
NCT00000412 Drug placebo calcitriol placebo calcitriol calcitriol
我想获取最大长度值为per的行(id, col1, col2, col3)
。
所需的输出是:
id col1 col2 col3 col4
NCT00000374 Drug olanzapine olanzapine olanzapine
NCT00000390 Drug imipramine hydrochloride imipramine hydrochloride imipramine hydrochloride
NCT00000412 Drug placebo calcitriol placebo calcitriol calcitriol
到目前为止,我尝试了以下查询,但均未成功:
select * from table1
where length(col4) = max(length(col4))
group by id, col1, col2, col3
order by id
一个案例DISTINCT ON
:
SELECT DISTINCT ON (id, col1, col2, col3)
*
FROM table1
ORDER BY id, col1, col2, col3, length(col4) DESC NULLS LAST;
最简单,每行几行,(id, col1, col2, col3)
通常也最快。详细说明:
对于大表和每组许多行,有(很多)更快的技术:
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句