sql-server - 按最新排序，但按另一个 ID 列放在一起

Question

我正在尝试进行一些排序并保持在一起(不是真正的分组)工作。在我的示例数据中，我希望将 DealerID 保留在一起，按 IsPrimaryDealer DESC 排序，但按最新条目显示经销商组(好吧，也许是分组)。

结果集 2 是最接近的，但 Grant 和他的兄弟应该按顺序显示为前两行。 (Grant 应该是第 1 行，Grants Brother 应该是第 2 行，因为 Grants Brother 是最近添加的)

DECLARE @temp TABLE (
    DealerPK int not null IDENTITY(1,1), DealerID int, 
    IsPrimaryDealer bit, DealerName varchar(50), DateAdded datetime
)

INSERT INTO @temp VALUES
(1, 1, 'Bob', GETDATE() - 7),
(2, 1, 'Robert', GETDATE() - 7),
(3, 1, 'Grant', GETDATE() - 7),
(3, 0, 'Grants Brother', GETDATE() - 1),
(2, 0, 'Roberts Nephew', GETDATE() - 2),
(1, 0, 'Bobs Cousin', GETDATE() - 3)

-- Data As Entered
SELECT * FROM @temp
-- Data Attempt at Row Numbering
SELECT *, intPosition = 
    ROW_NUMBER() OVER (PARTITION BY IsPrimaryDealer ORDER BY DealerID, IsPrimaryDealer DESC)
FROM @temp
ORDER BY DateAdded DESC
-- Data Attempt By DateAdded
SELECT *, intPosition = 
    ROW_NUMBER() OVER (PARTITION BY DealerID ORDER BY DateAdded DESC)
FROM @temp
ORDER BY intPosition, DateAdded

预期结果

PK  DID IsPr    Name            DateAdded
3    3  1       Grant           2015-10-08 17:14:26.497
4    3  0       Grants Brother  2015-10-14 17:14:26.497
2    2  1       Robert          2015-10-08 17:14:26.497
5    2  0       Roberts Nephew  2015-10-13 17:14:26.497
1    1  1       Bob             2015-10-08 17:14:26.497
6    1  0       Bobs Cousin     2015-10-12 17:14:26.497

Answer 1

根据OP的要求:

;WITH Cte AS(
    SELECT *, 
        mx = MAX(DateAdded) OVER(PARTITION BY DealerID) FROM @temp
) 
SELECT * 
FROM Cte 
ORDER BY mx DESC, DealerID, IsPrimaryDealer DESC