sql - PostgreSQL 获取一列为最小值的行

Question

我今天的第二个 PostgreSQL 问题...我确信解决方案很简单，但我只是不知道该怎么做。

我处理的表:

CREATE TABLE separabilities 
  ( 
     data_bands      TEXT[] NOT NULL, 
     thematic_class1 TEXT NOT NULL, 
     thematic_class2 TEXT NOT NULL, 
     jm_dist         DOUBLE PRECISION NOT NULL 
  ) 

我的查询:

select sep.data_bands,
       sum(sep.jm_dist)/count(sep.data_bands) as avarage_jm_dist, 
       min(jm_dist)
from separabilities as sep
group by sep.data_bands
order by avarage_jm_dist

我得到的:

我需要什么:

另外两个列，包含对应于最小距离的专题类。

像这样:data_bands -- avarage_jm_dist -- min -- thematic_class1 -- thematic_class2

Answer 1

像这样。

SELECT * 
FROM   (SELECT Row_number()OVER(partition BY data_bands ORDER BY jm_dist) AS RN, 
               data_bands, 
               thematic_class1, 
               thematic_class2
               Avg(sep.jm_dist)OVER(partition BY data_bands) as avarage_jm_dist
               jm_dist  
        FROM   separabilities) A 
WHERE  rn = 1 

或者您需要将结果加入主表以获得最小 jm_dist 和 data_bands

的 thematic_class1 和 thematic_class2

SELECT * 
FROM   separabilities A 
       INNER JOIN (SELECT sep.data_bands, 
                          Sum(sep.jm_dist) / Count(sep.data_bands) AS avarage_jm_dist
                          Min(jm_dist)                             AS jm_dist 
                   FROM   separabilities AS sep 
                   GROUP  BY sep.data_bands) B 
               ON A.data_bands = B.data_bands 
                  AND A.jm_dist = B.jm_dist 
ORDER  BY avarage_jm_dist