根据另一列的最大值获取值

I'm struggling to come up with a query to achieve the return data I require from the following table in SQL:

我很难想出一个查询来实现我需要的SQL中的下表返回数据:

I would like to get Value1 and Value2 for the maximum Order in a given Date and Period range. I have the following query to get the max order number for each Date Period pair in a given range - however, I can't seem to get the Value1 and Value2 for that order.

我想要在给定的日期和时间段内获得最大订单的Value1和Value2。我有以下查询来获取给定范围内每个日期周期对的最大订单号——但是，我似乎无法获得该订单的Value1和Value2。

SELECT 
    Date, Period, MAX(Order)
FROM 
    tableName
WHERE 
    ((Date = '2017-02-27' AND Period>= 10) OR (Date = '2017-02-28' AND Period<= 10))
GROUP BY
    Date, Period

3 个解决方案

#1

Use row_number():

使用row_number():

SELECT Date, Period, Order
FROM (SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY Date, Period ORDER BY Order DESC) as seqnum
      FROM tableName 
      WHERE (Date = '2017-02-27' AND Period >= 10) OR
            (Date = '2017-02-28' AND Period <= 10)
     ) t
WHERE seqnum = 1;

#2

You can use Window function row_number for this:

您可以使用窗口函数row_number进行如下操作:

select *
from (
    select t.*, row_number() over (
            partition by date, period order by [Order] desc
            ) rn
    from your_table t
    where (
            date = '2017-02-27'
            and Period >= 10
            )
        or (
            date = '2017-02-28'
            and Period <= 10
            )
    ) t
where rn = 1;

#3

If all you want is the top row of a result set, why not use the TOP operator?

如果您想要的只是结果集中的第一行，为什么不使用top操作符呢?

SELECT TOP 1 [Date], [Period], [Order], Value1, Value2
FROM tableName
WHERE ((Date = '2017-02-27' AND Period>= 10) OR (Date = '2017-02-28' AND Period<= 10))
ORDER BY [Order] DESC

#1