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?
Advertisement
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.)