SQL Server DENSE_RANK returns wrong result-CodePudding

I need to select the latest file in the taskID.

The id needs to change only when task or filenumber changes.

The file which has higher HistoryID should get id of 1, and subsequent files 2 .

Here is the current code, it's ranking when historyID changes as well. Last column is what I need:

DENSE_RANK() OVER (PARTITION BY taskid, filenumber 
                   ORDER BY HISTORYID DESC) AS id_file_to_keep

Any help much appreciated

CodePudding user response：

This is untested, as images of data don't help us help you (I can't copy text out of an image), however, perhaps this is what you want:

WITH Mins AS(
    SELECT taskid,
           historyID,
           filenumber,
           MIN(historyID) OVER (PARTITION BY taskid, filnumber) AS minHistoryID
    FROM dbo.YourTable)
SELECT taskid,
       historyID,
       filenumber,
       DENSE_RANK() OVER (PARTITION BY taskid ORDER BY minHistoryID DESC) AS DesiredID
FROM Mins;