Select a distinct row based on the max value of another column SQL Server 2008 R2

Question

I have a query that pulls in information from other tables that are created in the query. My final output from the final select from statement looks like this:

VISIT_ID | MRN | DAYS SCORE | IP SCORE | ER SCORE | CC SCORE | TOTAL
123456   | 123 | 3          | 3        | 2        | 0        |   8
123456   | 123 | 3          | 3        | 2        | 2        |   10
123456   | 123 | 3          | 3        | 2        | 4        |   12
...

What I would like to do is just get the row with the MAX(TOTAL), in this case the row with the TOTAL = 12

I have looked at this post but cannot seem to get it right. I have also looked here.

Here is the query that is producing the results:

-- @LACE_MSTR TABLE DECLARATION ###################################//
DECLARE @LACE_MSTR TABLE(
MRN VARCHAR(200)
, VISIT_ID VARCHAR(200)
, [LACE DAYS SCORE] INT
, [LACE ACUTE IP SCORE] INT
, [LACE ER SCORE] INT
, [LACE COMORBID SCORE] INT
)
--###################################################################//
INSERT INTO @LACE_MSTR
SELECT
Q1.MRN
, Q1.ENCOUNTER_ID
, Q1.[LACE DAYS SCORE]
, Q1.[ACUTE ADMIT SCORE]
, CASE
    WHEN Q1.VISIT_COUNT IS NULL THEN 0
    WHEN Q1.VISIT_COUNT = 1 THEN 1
    WHEN Q1.VISIT_COUNT = 2 THEN 2
    WHEN Q1.VISIT_COUNT = 3 THEN 3
    WHEN Q1.VISIT_COUNT >= 4 THEN 4
    ELSE 0
  END AS [LACE ER SCORE]
, Q1.[CC LACE SCORE]

FROM
    (
    SELECT
    DISTINCT T1.ENCOUNTER_ID
    , T1.MRN
    , T1.[LACE DAYS SCORE]
    , T1.[ACUTE ADMIT SCORE]
    , CNT.VISIT_COUNT
    , CM.[CC LACE SCORE]

    FROM @T1 T1
    LEFT OUTER JOIN @CNT CNT
    ON T1.MRN = CNT.MRN
    JOIN @CM CM
    ON CM.[MRN CM] = T1.[MRN]
    ) Q1

SELECT DISTINCT VISIT_ID
, MRN
, [LACE DAYS SCORE]
, [LACE ACUTE IP SCORE]
, [LACE ER SCORE]
, [LACE COMORBID SCORE]
, [LACE DAYS SCORE]+[LACE ACUTE IP SCORE]+[LACE ER SCORE]+[LACE COMORBID SCORE] AS [TOTAL LACE]

FROM @LACE_MSTR
Tried this but I did not do it right therefore it does not work
--INNER JOIN
--  (
--  SELECT VISIT_ID
--  , MAX([LACE DAYS SCORE]+[LACE ACUTE IP SCORE]+[LACE ER SCORE]+[LACE COMORBID SCORE]) AS [TOTAL LACE]
--  FROM @LACE_MSTR
--  GROUP BY VISIT_ID
--  ) GROUPEDLACE_MSTR ON @LACE_MSTR.VISIT_ID=GROUPEDLACE_MSTR.VISIT_ID
--  AND @LACE_MSTR.[TOTAL LACE SCORE] = GROUPED@LACE_MSTR.[TOTAL LACE]

GROUP BY VISIT_ID
, MRN
, [LACE DAYS SCORE]
, [LACE ACUTE IP SCORE]
, [LACE ER SCORE]
, [LACE COMORBID SCORE]

Please let me know if there is a need for clarification.

Thank you,

What is the actual grouping on (e.g. what dictates a duplicate), `VISIT_ID + MRN`? — Aaron Bertrand, Sep 03 '13 at 19:49
The duplicate comes from a separate query, It is very long, but from that query I am also grouping first on `VISIT_ID` — MCP_infiltrator, Sep 03 '13 at 19:54

score 1 · Accepted Answer · answered Sep 03 '13 at 19:52

1

Assuming grouping should be based on VISIT_ID and MRN:

;WITH x AS 
(
  SELECT VISIT_ID, MRN, ... etc ..., 
    rn = ROW_NUMBER() OVER (PARTITION BY VISIT_ID, MRN 
      ORDER BY [LACE DAYS SCORE] + [LACE ACUTE IP SCORE]
        + [LACE ER SCORE] + [LACE COMORBID SCORE] DESC)
  FROM @LACE_MSTR
)
SELECT VISIT_ID, MRN, ... etc ... 
FROM x WHERE rn = 1;

answered Sep 03 '13 at 19:52

Aaron Bertrand

272,866
37
466
490

Thank you @Aaron I will give this a try in the morning, have to clock out now. – MCP_infiltrator Sep 03 '13 at 19:54

Select a distinct row based on the max value of another column SQL Server 2008 R2

1 Answers1