I'm trying to determine what 'page' a particular row of a table is on. Each page is 300 rows long and ordered by a HammingWt and Id. To determine this, I need to count all rows above and including a particular row. A simplifed table might look like this:

Id  HammingWt  FileName
------------------------
1     0         F1.png
3     0         F7.png
2     1         F8.png
4     1         F2.png
12    2         F5.png
14    2         F3.png
15    2         F6.png
8     3         F4.png

Something like:

  SELECT COUNT(*)   1 FROM table [ABOVE] WHERE FileName = 'F3.png' ORDER BY HammingWt, ID;

And have it return 6 (the count of rows up to and including the F3.png row with Id = 14 when orderd by HammingWt and Id). I could then divide the returned count by the page length to determine which page the 'F3.png' row is on.

My actual table has approximately 1.5 * 10^6 rows. Id and FileName are unique, HammingWt is not.

Obviously, my select statement is not legal MS T-SQL since [ABOVE] is not a SQL keyword. Can someone show me a select statement that returns the count (as determined by the ORDER BY)?

I'm using MS SqlExpress (SQL Server 15.0.2).

CodePudding user response：

We can use row_number() and order it by HammingWt and Id.

select *
from   (
       select *
              ,row_number() over(order by HammingWt, Id) as rn
       from   t
       ) t
where  FileName = 'F3.png'

Fiddle