Count the number of rows above a given row when a particular is order is specified

d ei 0
Viewed 11

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

tsql select sql-server sql
1 Answers

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

select *
      ,row_number() over(order by HammingWt, Id) as rn
from   t
Id HammingWt FileName rn
1 0 F1.png 1
3 0 F7.png 2
2 1 F8.png 3
4 1 F2.png 4
12 2 F5.png 5
14 2 F3.png 6
15 2 F6.png 7
8 3 F4.png 8

Fiddle

DannySlor0
Related