How do I make an incremental ranking per query system on SQL Server 2019?

Currently on SQL Server 2019, I have the following code:

SELECT
    dbo.GroundTruth.QueryId,
    dbo.GroundTruth.Q0,
    dbo.GroundTruth.Id,
    RANK() OVER(ORDER BY dbo.GroundTruth.Similarity DESC) AS rank,
    dbo.GroundTruth.Similarity,
    dbo.GroundTruth.StValue FROM GroundTruth
    ORDER BY dbo.GroundTruth.QueryID ASC

that returns something like this this:

2639                 Q0   68556       118406               78.29591                                STANDARD
2639                 Q0   68582       143678               77.34721                                STANDARD
264                  Q0   6952        91193                79.59099                                STANDARD
264                  Q0   6951        207182               75.00000                                STANDARD
264                  Q0   6950        18046                88.25763                                STANDARD
2640                 Q0   68598       23812                87.26044                                STANDARD
2640                 Q0   68600       23776                87.27147                                STANDARD
2641                 Q0   68662       39200                84.62848                                STANDARD
2641                 Q0   68609       177816               76.06838                                STANDARD

However, I want the QueryID (1st) column to be ordered incrementally (1, 2, 3, 4, 5, …) instead of the current form (1, 10, 100, 1000, 10000) and I want the rank (4th) column to have ranks like this:

QueryID     ...     DocID     ...     rank     ...
1           ...     123       ...     1        ...
1           ...     247       ...     2        ...
1           ...     9876      ...     3        ...
2           ...     23        ...     1        ...
2           ...     17        ...     2        ...
2           ...     69        ...     3        ...
2           ...     09        ...     4        ...

Is there a property in SQL Server 2019 able to do this or does it need to envolve several functions?

Answer

The QueryId appears to be a string. You can convert that to an int. For the ranking, you want that per QueryId, so use PARTITION BY:

SELECT gt.*,
       RANK() OVER (PARTITION BY gt.QueryId ORER BY gt.SImilarity DESC) as rank
FROM GroundTruth gt
ORDER BY TRY_CONVERT(int, gt.QueryID) ASC;

(This uses gt.* to focus on the columns you care about in the question. In general, though, use table aliases so queries are easier to write and read.)

If you want the QueryId actually replaced with an enumerated value, then use:

SELECT DENSE_RANK() OVER (ORDER BY gt.QueryId)

I’m not sure if you would want such an ordering based on the string or the number value of the string. For number sorting, you can use:

SELECT DENSE_RANK() OVER (ORDER BY CONVERT(int, gt.QueryId))

(This all assumes that int is big enough for the values. If not use NUMERIC/DECIMAL.)