sql - 查询以从多行中获取一个条目

Question

查询:

select machinename, StatusCode, max(size) as size, statusID
from machine where MachineID In( '33','22') and StatusCode = 166 
group by machinename, StatusCode, statusID
order by max(size) DESC

结果:

   machinename  StatusCode  size statusID
    -----------  ----------  ---- --------
    test1        166         50       1
    test1        166         25       2
    test2        166         75       3
    test2        166         48       4

要求:

我只需要为每台机器显示一个条目。我必须通过获取两个条目之间的最大大小值来做到这一点，如上所示。就像 test1 我有两种尺寸 50 和 25 我必须显示有 50 的行并忽略有 25 的行。

谢谢

期望的结果:

 machinename  StatusCode  size statusID
    -----------  ----------  ---- --------
    test1        166         50       1  
    test2        166         75       3 

Answer 1

SELECT machinename, StatusCode, size, statusID
FROM (
    SELECT
        machinename,
        StatusCode,
        size,
        statusID,
        ROW_NUMBER() OVER (PARTITION BY MachineID ORDER BY size DESC) AS rn
    FROM machine
    WHERE MachineID IN ('33','22')
    AND StatusCode = 166 
) T1
WHERE rn = 1
ORDER BY size DESC