按最近排序但按另一个 ID 列保持在一起

问题描述

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

I am trying to get some sorting and keep together (not really grouping) working.In my sample data I would like to keep the DealerIDs together, sorted by IsPrimaryDealer DESC, but show the group (ok maybe it is grouping) of dealers by the ones with the most recent entry.

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

Result set 2 is the closest, but Grant and his brother should be displayed as the first two rows, in that order. (Grant should be row 1, Grants Brother row 2 because Grants Brother was the most recently added)

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

推荐答案

根据 OP 的要求:

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

这篇关于按最近排序但按另一个 ID 列保持在一起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！