sql - WITH TIES 不根据记录类型过滤记录

问题描述

我写了一个 sql 来获取基于版本的最新记录。它确实提取了最新记录，但我需要确保它提取每种类型的最新记录。类型由 AgreementId 确定。正如您在下面的快照中看到的，有两条具有不同协议 ID 的记录。我正在尝试使用领带，但这似乎不起作用。我究竟做错了什么

这些是当前记录

下面的查询仅从两条记录中获取一条最新一致的记录。理想情况下，它应该返回两者，因为它们是两种类型的协议。我正在使用 WITH TIES，但这似乎并没有做我想要的

  SELECT TOP (1) WITH TIES  ua.ID AS UserAgreementID ,
         A.ID AS AgreementID ,
         A.Code ,
         A.ComplianceCode ,
         A.Name ,
         A.Description ,
         A.Version ,
         ua.UserAgreementStateID ,
         uas.Name AS UserAgreementStateName ,
         ua.AcceptanceWindowExpiry ,
         declaration.GetDifferenceInDaysOrHours(ua.AcceptanceWindowExpiry) AS TimeLeft ,
         A.Data ,
         pa.ID AS AuthoredByID ,
         pa.FirstName + ' ' + pa.LastName AS AuthoredByName ,
         A.Authored ,
         ia.ID AS IssuedByID ,
         ia.FirstName + ' ' + pa.LastName AS IssuedByName ,
         A.Issued
  FROM declaration.Agreement AS A
  INNER JOIN declaration.UserAgreement AS ua ON A.ID = ua.AgreementID
  INNER JOIN declaration.UserAgreementState AS uas ON ua.UserAgreementStateID = uas.ID
  LEFT JOIN common.Person AS pa ON A.AuthoredBy = pa.ID
  LEFT JOIN common.Person AS ia ON A.IssuedBy = ia.ID WHERE ua.UserID = 607
  AND uas.Code IN ('ISS',
                   'DEF','EXP')-- Issued, Deferred

  AND A.Draft = CONVERT(BIT, 0) -- Not a draft.

  AND A.Deleted = CONVERT(BIT, 0) -- Not deleted.

  AND (A.Issued <= GETUTCDATE()
       OR A.Issued IS NULL)
  AND (A.Expires > GETUTCDATE()
       OR A.Expires IS NULL)
ORDER BY A.Version DESC

标签： sqlsql-servertsqlsql-order-bygreatest-n-per-group

我认为你想要：

ORDER BY ROW_NUMBER() OVER(PARTITION BY A.AgreementID ORDER BY A.Version DESC)

该函数通过降序row_number()排列具有相同的记录。换句话说，对于每个，该函数将排名分配给具有最大的记录。然后，过滤所有排名第一的记录。AgreementIDVersionAgreementID1VersionTOP (1) WITH TIES

sql - WITH TIES 不根据记录类型过滤记录

问题描述

解决方案

推荐阅读