时间戳

首页 > 解决方案 > 在sql中检索最新结果

sql - 在sql中检索最新结果

问题描述

我已经编写了提取协议数据的 sql 查询。我需要提取最新的协议。最新版本是根据最新版本确定的。如您所见，目前我的查询正在显示两条记录。请注意，版本是 varchar 字段

询问

SELECT 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 UA.AcceptanceWindowExpiry ASC

在此处输入图像描述

如您所见，有两个版本。我需要我的查询应该只提取 2.1.0.000 版本。我需要做最大的版本或任何其他方式吗？如果我执行 Max() ，它会在顶部显示最新记录，但我只需要最新记录而不需要第二条记录

标签： sqlsql-server

解决方案

您可以使用 top (1)：

SELECT TOP (1) ua.ID AS UserAgreementID, A.ID AS AgreementID,
         . . . .
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') AND -- Issued, Deferred  
      A.Draft = CONVERT(BIT, 0) AND -- Not a draft.
      A.Deleted = CONVERT(BIT, 0) AND -- Not deleted.
     (A.Issued <= GETUTCDATE() OR A.Issued IS NULL) AND 
     (A.Expires > GETUTCDATE() OR A.Expires IS NULL)
ORDER BY A.Version DESC;

推荐阅读