postgresql中的sql问题

Question

SELECT districtid, char_length(districtid) as d_length FROM district
       where ST_Intersects(geometry, ST_GeometryFromText('LINESTRING (119.922 30.526, 120.168 30.596)')) 

查询出来的结果：

接下来我怎么将最长length的行过滤出来，这张图片里面是length为9的行。
现在我用的是with语句：

With dis AS
(SELECT districtid, char_length(districtid) as d_length FROM district
       where ST_Intersects(geometry, ST_GeometryFromText('LINESTRING (119.922 30.526, 120.168 30.596)')) )
Select * From dis where d_length=(select max(d_length) from dis);

有没有其他更好的方法？

Answer 1

可以考虑窗口函数dense_rank()：

with dis as (
  select districtid, char_length(districtid) as d_length,
         dense_rank() over (order by char_length(districtid)) as dr
  from ...)

select districtid, d_length from dis where dr=1

效率上应该差不多吧。