如何对一个ID字段进行分组并选择另一个字段中具有最高值的行PostgreSQL

user3814846 发表于 Dev

用户名

我不知道应该为我的问题给什么标题（没关系）。下面给出的是我的选择查询

select gtab04.product,gtab05.productid,gtab05.mrp, gtab05.ptr,gtab05.ssr,gtab07.patent from gtab05 inner 
join gtab07 on gtab05.patentid=gtab07.patentid inner join gtab04 on 
gtab05.productid=gtab04.productid  where gtab05.qty-gtab05.iqty > 0 order by productid

这将返回500+行，请参见以下示例，

product           |productid   |mrp     |ptr  |ssr  |patent
------------------+------------+--------+-----+-----+----------------- 
IBUGESIC Plus Tab |200         |12.80000|9.85 |8.87 |CIPLA LTD 
ANGICAM 2.5 Tab   |267         |9.00000 |6.93 |6.44 |BLUE CROSS LABORATORIES 
ANGICAM 2.5 Tab   |267         |5.00000 |6.93 |6.24 |BLUE CROSS LABORATORIES 
ANGICAM 2.5 Tab   |267         |5.00000 |6.93 |6.44 |BLUE CROSS LABORATORIES 
ANGICAM 2.5 Tab   |267         |5.00000 |7.359|6.24 |BLUE CROSS LABORATORIES 
ANGICAM 5 Mg Tab  |268         |14.00000|10.78|10.03|BLUE CROSS LABORATORIES 
ANGICAM 5 Mg Tab  |268         |12.00000|11.44|9.7  |BLUE CROSS LABORATORIES 
ANGICAM BETA Tab  |269         |17.00000|13.09|12.17|BLUE CROSS LABORATORIES 
ANGICAM BETA Tab  |269         |15.00000|13.9 |11.78|BLUE CROSS LABORATORIES 
HIBESOR 25 TAB    |270         |9.00000 |6.93 |6.44 |BLUE CROSS LABORATORIES

我想将以上结果修改如下。

product           |productid   |mrp     |ptr  |ssr  |patent
------------------+------------+--------+-----+-----+----------------- 
IBUGESIC Plus Tab |200         |12.80000|9.85 |8.87 |CIPLA LTD 
ANGICAM 2.5 Tab   |267         |9.00000 |6.93 |6.44 |BLUE CROSS LABORATORIES 
ANGICAM 5 Mg Tab  |268         |14.00000|10.78|10.03|BLUE CROSS LABORATORIES 
ANGICAM BETA Tab  |269         |17.00000|13.9 |11.78|BLUE CROSS LABORATORIES 
HIBESOR 25 TAB    |270         |9.00000 |6.93 |6.44 |BLUE CROSS LABORATORIES

我的标准：需要GROUP productid并且从每个组都product拥有max(mrp)。

到目前为止我尝试过的。

    With cte as (
    select gtab04.product,gtab05.productid,gtab05.mrp, gtab05.ptr,gtab05.ssr,patent from gtab05 
    inner join gtab07 on gtab05.patentid=gtab07.patentid inner join gtab04 on 
    gtab05.productid=gtab04.productid  where qty-iqty > 0 order by productid limit 10
    )
    select productid,max(cte.mrp) as mrp  from cte group by productid order by  productid
    )

         RESULT
    --------------
   productid | mrp
          200|12.80000
          267|9.00000
          268|14.00000
          269|17.00000
          270|9.00000

克洛多尔多·内托

distinct on 结合 order by

select distinct on (productid) 
    gtab04.product,
    productid,
    gtab05.mrp,
    gtab05.ptr,
    gtab05.ssr,
    patent
from
    gtab05
    inner join
    gtab07 using(patentid)
    inner join
    gtab04 using(productid)
where qty-iqty > 0
order by productid, gtab05.mrp desc

如果还有其他解开条件，则将其添加到order by子句中

http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。