SQL using Rank and Row_Number with Order by

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.

Dx

DxDate

Row_number

CreateDate

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:

Rank()

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!

3 Answers
3

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

ROW_NUMBER

ORDER BY

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."

DocID

DxDate

CreateDate

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.

DocID

DocID

You can use only ROW_NUMBER() and ORDER BY CreateDate it will work.

ROW_NUMBER() OVER (PARTITION BY DocID ORDER BY CreateDate DESC) AS rownum1

According to your sample data just need below query

SELECT top 1 *
FROM DxList
order by DxDate desc,CreateDate desc


But According to your description follow @Tim provided query

here in this link both query you will find

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=44fdd4b2e849137cebe9df837ac41a39

DocID Dx DxDate CreateDate
6018 ADHD 01/10/2015 00:00:00 09/10/2015 18:14:00


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.