CODEWITHSUNDEEP
sqlsql-serverwindow-functionsrow-number
WixLove
WixLove

Reputation: 73

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.

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!

Upvotes: 0

Views: 361

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

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.

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

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

Upvotes: 0

Shivam
Shivam

Reputation: 116

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

Upvotes: 0

Related Questions