如何在SQL中为每个组选择最大行

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 )

我希望选择“价值”最大值的国家/地区作为“grpid”。在检查最大值时,也不应考虑其他'grpid'选择'Country'。 (即不应在结果中重复国家或grpid)

SQL Fiddle

Result:

Country    grpid        Value           Row_number

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

3 个解决方案

#1

I believe this is what you're looking for:

我相信这就是你要找的东西:

SQL Fiddle

;with cte as 
(
  select 
      country,
      max(value) as MaxVal,
      min(row_number) as MinRow
  from test1
  group by Country
)
select 
  c.country,
  t.grpid,
  c.MaxVal,
  c.MinRow
from cte c
join test1 t
  on t.country = c.country 
  and t.value = c.MaxVal
  and t.row_number = c.MinRow
order by country, grpid

#2

try this query instead,

试试这个查询,

  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

#3

Can you please try out this query

你能试试看吗?

select 
    country,
    value,
    grpid,
    count(*) 
from test1 
group by  
    country,
    value,
    grpid 
order by 
    country,
    value desc

#1