How to select row with max value when duplicate rows exist in SQL Server

sql, sql-server

Solution

You're basically just missing a status comparison since you want one row per status;

SELECT *
FROM WF_Approval sr1
WHERE NOT EXISTS (
    SELECT *
    FROM  WF_Approval sr2 
    WHERE sr1.DocumentID = sr2.DocumentID AND 
          sr1.Status = sr2.Status AND                  # <-- new line
          sr1.StepNumber < sr2.StepNumber
) AND MasterStepID = 'Approval1'

or rewritten as a `JOIN`;

SELECT *
FROM WF_Approval sr1
LEFT JOIN WF_Approval sr2
  ON sr1.DocumentID = sr2.DocumentID 
 AND sr1.Status = sr2.Status
 AND sr1.StepNumber < sr2.StepNumber
WHERE sr2.DocumentID IS NULL
  AND sr1.MasterStepID = 'Approval1';

SQLfiddle with both versions of the query here.

Problem

I have table like this ``` DocumentID | MasterStepID | StepNumber | RoleID | UserID | Status JIEP/TT/07/000174 | Approval1 | 1 | NULL | 0006100022 | 1 JIEP/TT/07/000174 | Approval1 | 2 | 12 | 0006199013 | 3 JIEP/TT/07/000174 | Approval1 | 3 | 13 | 0006106426 | 3 JIEP/TT/07/000174 | Approval1 | 5 | 18 | 0006100022 | 3 JIEP/TT/07/000174 | Approval1 | 6 | 16 | 0006104115 | 6 ``` I expect result like this ``` JIEP/TT/07/000174 | Approval1 | 1 | NULL | 0006100022 | 1 JIEP/TT/07/000174 | Approval1 | 5 | 18 | 0006100022 | 3 JIEP/TT/07/000174 | Approval1 | 6 | 16 | 0006104115 | 6 ``` I try this query but it's return not like what I expect ``` select * from WF_Approval sr1 where not exists ( select * from WF_Approval sr2 where sr1.DocumentID = sr2.DocumentID and ( sr1.StepNumber < sr2.StepNumber ) )and MasterStepID = 'Approval1' ```

Original source