选择一列的顶部1，根据重复ID与其他唯一字段进行分组

Question

根据在不同的列(Account_Occupant)中选择最高的序列，我正在为表中的每个帐户(千)只需要一行的数据进行查询。

我需要返回一个不同的帐号(90006)，最高的account_occupant (11)和对应于该account_occupant (迈克尔)的名称。以下是一个数据示例：

Account_NUM Account_Occupant    Name
90006           1               JOHN
90006           2               MARY
90006           3               MARY
90006           4               KERRI
90006           5               PATRICIA
90006           6               DARYL
90006           7               ASHLEY
90006           8               DARYL
90006           9               DIANE
90006           10              DARYL
90006           11              MICHAEL

我需要返回的一个例子是：

Account_NUM Account_Occupant    Name
90006           11              MICHAEL

下面是最近的查询尝试：

    SELECT 
    DISTINCT CIS.Account_NUM
        ,(
            SELECT top 1 CISa.Account_Occupant 
            FROM database.view CISa 
            WHERE CISa.Account_NUM = CIS.Account_NUM 
            ORDER BY CISa.Account_Occupant DESC
         ) AS Newest_Occupt_Num
    , CIS.name
    FROM database.view CIS
    WHERE EXISTS 
        (
            SELECT TOP 1 CIS2.Account_Occupant 
            FROM database.view CIS2 
            WHERE CIS.Account_NUM = CIS2.Account_NUM 
            AND CIS.Account_Occupant = CIS2.Account_Occupant 
            ORDER BY CIS2.Account_NUM, CIS2.Account_Occupant DESC
        )
    ORDER BY 1,2

我已经做了相当多的搜索和尝试的变化，小组一组，在其中的声明，子选择和联合声明，我在这个论坛上找到，但仍然没有得到正确的。为编辑向人们提问表示歉意，因为这些问题指出了我提问中的错误--非常感谢。

Answer 1

另一种你可以尝试的方法。

CREATE TABLE TBL(Account_NUM VARCHAR(100),Account_Occupant INT,Name VARCHAR(100))

INSERT INTO TBL
SELECT '123',11,'ABC'

INSERT INTO TBL
SELECT '123',12,'XYZ'


INSERT INTO TBL
SELECT '124',11,'ABC'


INSERT INTO TBL
SELECT '124',12,'XYZ'


SELECT DISTINCT T.*, T1.Name FROM
(
   SELECT Account_NUM, MAX(Account_Occupant) OVER (PARTITION BY Account_NUM) AS MAX_Account_Occupant
   FROM TBL
 ) T
 INNER JOIN TBL T1 ON T1.Account_NUM= T.Account_NUM AND T1.Account_Occupant=T.MAX_Account_Occupant

输出

    Account_NUM MAX_Account_Occupant    Name
   ------------ --------------------   ----- 
    123               12                XYZ
    124               12                XYZ

Answer 2

如果我正确理解了你的描述，你就不需要分组了。一个简单的降序会给你想要的：

SELECT TOP 1 Account_NUM, Account_Occupant, Name
FROM table1
ORDER BY Account_Occupant DESC

如果您的数据库中除了Account_NUM之外还有其他记录，而且您只想要这个Account_NUM，那么只需添加一个条件：

SELECT TOP 1 Account_NUM, Account_Occupant, Name
FROM table1
WHERE Account_NUM = 90006
ORDER BY Account_Occupant DESC

Answer 3

如果您正在寻找每一个( Account_Num )中的前1位，这只是另一种选择。

我之所以提交这个，是因为你正在寻找一组成员并加入。

Select Top 1 with ties *
 From  YourTable 
 Order By Row_Number() over (Partition By Account_NUM Order By Account_Occupant Desc)