如何在SQL中为每个组选择一个最大行 [英] How to select a max row for each group in SQL

问题描述

我想要为"grpid"选择最大值为"Value"的国家/地区.另外，在检查最大值时，不应将已经选择的国家/地区"考虑为其他粗俗". (即，不应在结果中重复国家或地区")

I want select countries with maximum value of 'Value' for a 'grpid'. Also already selected 'Country' should not be considered for other 'grpid' while checking the maximum. ( ie Country or grpid should not be repeated in the result )

SQL提琴

SQL Fiddle

结果:

Result:

Country    grpid        Value           Row_number

US        49707        604456458         1
GB        5086         497654945         4 
CA        909          353500201         10
JP        231          198291290         15

推荐答案

请尝试执行此查询，

  WITH OrderedOrders AS
  (
     SELECT country,grpid,value,ROW_NUMBER() OVER(PARTITION BY country ORDER BY   country,value DESC) AS 'RowNumber' 
     FROM test1
  ) 
 select * from  OrderedOrders
 where RowNumber =1

这篇关于如何在SQL中为每个组选择一个最大行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！