SQL using Rank and Row_Number with Order by

Question

I am using SSMS 2018 and have a table similar to this:

CREATE TABLE DxList (DocID INT, Dx VARCHAR(255), DxDate DATE, CreateDate DATETIME);

INSERT INTO DxList (DocID, Dx, DxDate, CreateDate)
VALUES (6018, 'OSDD', '10/01/2015', '10/09/2015 12:27');

INSERT INTO DxList (DocID, Dx, DxDate, CreateDate)
VALUES (6018, 'ADHD', '10/01/2015', '10/09/2015 18:14');

---

SELECT *
FROM DxList

DocID   Dx      DxDate      CreateDate       
6018    OSDD    10/1/2015   10/9/2015 12:27   
6018    ADHD    10/1/2015   10/9/2015 18:14 

I'd like to get the most recent Dx based on the DxDate. Row_number would work, however, if there is a tie like the one above (10/1/2015), I want the most recent to be based on the CreateDate instead.

In this case, I'd like my result to be:

DocID   Dx      DxDate      CreateDate        
6018    ADHD    10/1/2015   10/9/2015 18:14  

I believe I can use Rank() with this query but I'm not sure how. This is my query so far:

SELECT DISTINCT
  DocID,
  Dx,
  DxDate,
  CreateDate,
  rownum1,
  rank1
FROM (SELECT
  DocID,
  Dx,
  DxDate,
  CreateDate,
  RANK() OVER (PARTITION BY DocID ORDER BY DxDate DESC) AS rank1,
  ROW_NUMBER() OVER (PARTITION BY DocID ORDER BY DxDate DESC) AS rownum1
FROM DxList) b
WHERE rownum1 = 1

I believe I'm looking for something like:

Order By (Case When rank1=rank1 then CreateDate else DxDate End) DESC

Of course this doesn't work because I need some sort of iteration and can't put it in the subquery. I also cannot use any derived tables to solve this issue. Is this possible? Much appreciated!

Answer 1

You should be able to use ROW_NUMBER with a two-tiered ORDER BY clause:

SELECT DocID, Dx, DxDate, CreateDate
FROM
(
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY DocID ORDER BY DxDate DESC, CreateDate DESC) rn
    FROM DxList
) t
WHERE rn = 1;

The only major change in the above query versus what you had in mind is that two records with the same DocID and DxDate values would then be compared by the CreateDate to decide which one is really the "first."

Demo

Note: In your demo the two records have different DocID values, but I think you intended for them to have the same value. In the demo link above, I have changed the two records to have the same DocID value, and the logic is working.