0

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

d ei
  • 493
  • 4
  • 16

1 Answers1

2

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'
Id HammingWt FileName rn
14 2 F3.png 6

Fiddle

DannySlor
  • 4,574
  • 1
  • 3
  • 11
  • I think it's close, but your fiddle returns 1, not 6. – d ei Sep 26 '22 at 01:22
  • @dei I'm not sure what you mean. My fiddle returns the result I posted. Do you need it to do something different? – DannySlor Sep 26 '22 at 01:29
  • I wanted the row number of a particular row (in my question, the row with Id = 14 and FileName = 'F3.png') which is 6. Your answer pointed me in the right direction (to use row_number()), though. Additional help from https://stackoverflow.com/questions/8738785/how-to-find-row-number-of-a-record gave me the answer I was looking for: WITH OrderedTable as (SELECT *, row_number() OVER(ORDER BY HammingWt, Id) as 'RowNumber' from t) SELECT RowNumber FROM OrderedTable WHERE FileName = 'F3.png'; – d ei Sep 26 '22 at 02:19
  • Change your select statement to – d ei Sep 26 '22 at 02:19
  • Change your select staement to: WITH OrderedTable as (SELECT *, row_number() OVER(ORDER BY HammingWt, Id) as 'RowNumber' from t) SELECT RowNumber FROM OrderedTable WHERE FileName = 'F3.png'; – d ei Sep 26 '22 at 02:20
  • and I'll mark it as the answer (sorry, I'm having trouble adding markup to my comments) – d ei Sep 26 '22 at 02:21
  • 1
    @dei See updated answer – DannySlor Sep 26 '22 at 13:03
  • Your query was better than the one I came up with. Thanks! – d ei Sep 26 '22 at 15:04