Retrieving the latest result in sql

I have written sql query that is pulling agreements data. I need to pull the latest agreement. The latest version is determined based on the most recent version. As you can see currently my query is displaying two records. Please note that version is varchar field

Query

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

As you can see there are two versions. I need my query should pull only 2.1.0.000 version. Do I need to do max of the version or any other way ? If I do Max() , it shows the latest record on the top but i need only the latest and not the second record

Answer

You can use 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;