how to use having clause in sql query in my result set that I am getting

Question

SELECT  
            P.PK_PatientId
            ,PV.PK_PatientVisitId
            --, PV.LastUpdated
            , ISNULL(P.FName,'')+ ', '+ ISNULL(P.LName,'') AS NAME
            , MAX(TVP.PK_VisitProcedureId) AS PK_VisitProcedureId
            , MAX(PV.LastUpdated)AS DateSort
        FROM         
            dbo.M_Patient AS P 
                INNER JOIN
            dbo.M_PatientVisit AS PV 
                    ON 
                        P.PK_PatientId = PV.FK_PatientId 
                INNER JOIN
            dbo.TX_VisitProcedure AS TVP 
                    ON 
                        PV.PK_PatientVisitId = TVP.FK_PatientVisitId
        WHERE     
            (P.IsActive = 1) 
                AND 
            (PV.IsActive = 1) 
                AND 
            (TVP.IsActive = 1)
            --  AND
            --PV.LastUpdated=GETDATE()
        GROUP BY PK_PatientId, PK_PatientVisitId, ISNULL(P.FName,'')+ ', '+ ISNULL(P.LName,'')--, PV.LastUpdated
        --HAVING MAX(TVP.PK_VisitProcedureId)=PK_VisitProcedureId
        ORDER BY 
            P.PK_PatientId DESC
            , DateSort DESC
            ,PV.PK_PatientVisitId DESC

PK_PatientId PK_PatientVisitId NAME                  PK_VisitProcedureId    DateSort
------------ ----------------- --------------------  ------------------- -----------------------
3            5                 TRAVIS, BARKER           8                   2010-08-31 00:00:00.000
3            3                 TRAVIS, BARKER           6                   2010-08-31 00:00:00.000
2            2                 BRIAN, IGNOTOWICZ        5                   2010-08-23 00:00:00.000
1            4                 WILLIAM, HENWOOD         7                   2010-08-31 00:00:00.000
1            1                 WILLIAM, HENWOOD         4                   2010-08-31 00:00:00.000

(5 row(s) affected)

Above mentioned query is returning me the above given result. I want to get only distinct rows for each patient where PK_VisitProcedureId is maximum in the result set. I think this can be done using Having clause in the above query but not been able to get it.

Desired Result set is

PK_PatientId PK_PatientVisitId NAME                  PK_VisitProcedureId    DateSort
------------ ----------------- --------------------  ------------------- -----------------------
3            5                 TRAVIS, BARKER           8                   2010-08-31 00:00:00.000
2            2                 BRIAN, IGNOTOWICZ        5                   2010-08-23 00:00:00.000
1            4                 WILLIAM, HENWOOD         7                   2010-08-31 00:00:00.000

Answer 1

SELECT PK_PatientId, PK_PatientVisitId, NAME, PK_VisitProcedureId, DateSort
FROM   (SELECT t.*, ROW_NUMBER() OVER(PARTITION BY PK_PatientId ORDER BY    
        PK_VisitProcedureId DESC) r
     FROM   (SELECT P.PK_PatientId, PV.PK_PatientVisitId,
                     ISNULL(P.FName, '') + ', ' + ISNULL(P.LName, '') AS NAME,
                     MAX(TVP.PK_VisitProcedureId) AS PK_VisitProcedureId,
                     MAX(PV.LastUpdated) AS DateSort
              FROM   dbo.M_Patient AS P
              INNER  JOIN dbo.M_PatientVisit AS PV
              ON     P.PK_PatientId = PV.FK_PatientId
              INNER  JOIN dbo.TX_VisitProcedure AS TVP
              ON     PV.PK_PatientVisitId = TVP.FK_PatientVisitId
              WHERE  (P.IsActive = 1)
              AND    (PV.IsActive = 1)
              AND    (TVP.IsActive = 1)
              GROUP  BY PK_PatientId, PK_PatientVisitId,
                        ISNULL(P.FName, '') + ', ' + ISNULL(P.LName, '')
              ORDER  BY P.PK_PatientId DESC, DateSort DESC, PV.PK_PatientVisitId DESC
  ) t ) e
  WHERE  r = 1;

Also you can try: (sub-query select latest visits for each patient)

 SELECT P.PK_PatientId, PV.PK_PatientVisitId,
         ISNULL(P.FName, '') + ', ' + ISNULL(P.LName, '') AS NAME,
         MAX(TVP.PK_VisitProcedureId) AS PK_VisitProcedureId, DateSort
  FROM   (SELECT PV.FK_PatientId PatientId, MAX(PK_PatientVisitId) PatientVisitId,
                  MAX(PV.LastUpdated) AS DateSort
           FROM   dbo.M_PatientVisit AS PV AND (PV.IsActive = 1)
           GROUP  BY PV.FK_PatientId) AS LatestVisits, M_Patient AS p, TX_VisitProcedure AS tvp
  WHERE  p.PK_PatientId = LatestVisits.PatientId
  AND    tvp.FK_PatientVisitId = LatestVisits.PatientVisitId
  AND    (P.IsActive = 1)
  AND    (TVP.IsActive = 1)
  GROUP  BY PK_PatientId, PK_PatientVisitId, ISNULL(P.FName, '') + ', ' + ISNULL(P.LName, ''),
            DateSort
  ORDER  BY 1 DESC, DateSort DESC