如何在组内订购值

Consider the following SQL Server table:

请考虑以下SQL Server表:

  ID  |   X   |   Y
------+-------+-------
   1  |   1   |   1
   2  |   1   |   2
   3  |   1   |   3
   4  |   2   |   40
   5  |   2   |   500
   6  |   3   |   1
   7  |   3   |   100
   8  |   3   |   10

I need to select the ID of the row that has the maximum value of Y grouped by x, i.e:

我需要选择Y的最大值为Y的行的ID,即:

  ID  |   X   |   Y
------+-------+-------
   3  |   1   |   3
   5  |   2   |  500
   7  |   3   |  100

The query will be nested several times so an optimal performance solution is required...

查询将嵌套多次,因此需要最佳性能解决方案......

4 个解决方案

#1

Setup:

 declare @MyTable table(ID int, X int, Y int)

 insert @MyTable
 values
    (   1  ,   1   ,   1),
    (   2  ,   1   ,   2),
    (   3  ,   1   ,   3),
    (   4  ,   2   ,   40),
    (   5  ,   2   ,   500),
    (   6  ,   3   ,   1),
    (   7  ,   3   ,   100),
    (   8  ,   3   ,   10)

Query:

;with cte
as
(
    select *, row_number() over(partition by X order by Y desc) RowNumber
    from @MyTable
)
select Id, X, Y
from cte
where RowNumber = 1

Result:

Id          X           Y
----------- ----------- -----------
3           1           3
5           2           500
7           3           100

The query is for MS SQL 2005+. The setup will work in MS SQL 2008+.

该查询适用于MS SQL 2005+。该设置将在MS SQL 2008+中使用。

#2

Yeah, many times this type of question... Added keyword "STRAIGHT_JOIN" to pre-optimize the query by enforcing the "PreQuery" first.

是的,很多时候这类问题...添加关键字“STRAIGHT_JOIN”以通过首先强制执行“PreQuery”来预优化查询。

select STRAIGHT_JOIN
      YT.*
   from 
      ( select x, max(y) HighPerX
           from YourTable 
           group by x ) PreQuery
      join
         YourTable YT
            on PreQuery.X = YT.X 
            AND PreQuery.HighPerX = YT.y

#3

This is a classic:

这是一个经典:

The Rows Holding the Group-wise Maximum of a Certain Column

行保持某一列的组最大值

#4

Also see what you can do with row_number() function http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

另请参阅使用row_number()函数可以执行的操作http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

#1