SQL Server中其他字段的Max

Question

以下是我的疑问：

SELECT id, id_gamut, position from operation

它返回：

N°1)    id    id_gamut    position
        --------------------------
         1        19           1
         2        19           2
         3        19           3
         4        25           1
         5        25           2
         6        12           1

我需要将其按id_gamut与最大位置进行分组，以获得如下结果：

N°2)    id    id_gamut    position
        --------------------------   
         3        19           3    
         5        25           2
         6        12           1

然后我尝试了这样的方法：

SELECT gamut, Max(position) from(
SELECT id, gamut, position from operation) as req
GROUP BY gamut

它可以工作，但我的问题是，我确实需要在查询中包含'id‘字段，但是如果我像这样添加它：

SELECT id, gamut, Max(position) from(
SELECT id, gamut, position from operation) as req
GROUP BY gamut,id

我的小组被打破了，我得到了一个类似于N°1的结果。

我如何用最大位置和“id”字段按id_gamut进行分组？

Answer 1

带领带的顶部与row_number()的结合

select top 1 with ties
    id
  , id_gamut
  , position
from operation
order by row_number() over (
  partition by id_gamut 
  order by position desc
)

或者将公共表表达式与row_number()结合使用

;with cte as (
  select *
    , rn = row_number() over (
      partition by id_gamut 
      order by position desc
    )
  from operation
)
select 
    id
  , id_gamut
  , position
from cte
where rn = 1

或者作为一个没有cte的子查询

select 
    id
  , id_gamut
  , position
from  (
  select *
    , rn = row_number() over (
      partition by id_gamut 
      order by position desc
    )
  from operation
) s
where rn = 1

或使用交叉适用()

select distinct 
    x.id
  , o.id_gamut
  , x.position
from operation o
  cross apply (
    select top 1
        id
      , position
    from operation i
    where i.id_gamut = o.id_gamut
    order by position desc
    ) x

或者使用不存在() (如果有超过一行的最大位置，则每id_gamut将返回多于1行)

select *
from operation o
where not exists (
    select 1
    from operation i
    where i.id_gamut = o.id_gamut
     and i.position > o.position
    )

或使用不存在() (在发生具有相同最大位置的多行时，额外子句返回最高id )

select *
from operation o
where not exists (
    select 1
    from operation i
    where i.id_gamut = o.id_gamut
      and (i.position > o.position
           or (i.position = o.position and i.id > o.id)
          )
  )

rextester演示：http://rextester.com/INV77202

Answer 2

在公共表表达式中使用ROW_NUMBER()

WITH CTE AS 
(
   SELECT id, id_gamut, position 
   , ROW_NUMBER() OVER (PARTITION BY id_gamut ORDER BY position DESC) AS Rn   
   FROM operation
)
SELECT * FROM CTE WHERE Rn = 1

Answer 3

使用MAX函数和GROUP BY子句：

 SELECT  *
 FROM table1
 JOIN 
 (
   SELECT id_gamut,MAX(position) position
   GROUP BY  id_gamut
 ) A ON A.id_gamut = table1.id_gamut AND A.position = table1.position