MySql ROW_NUMBER not numbering as expected-CodePudding

I'm using a query that is using the ROW_NUMBER function and it's not working as I expecting. My understanding of the window functions is that any change in the data will reset the row counter. However it looks like it will resume numbering when it encounters the same combination again. So because (31.7845,-72.52) was found before, row 2267 is '35', not the '1' I was expecting it to be. Is there a way to create this functionality?

row_number() over
    (partition by FirebaseUserId, Latitude, Longitude
     order by FirebaseUserId, Id) as 'rownum'

CodePudding user response：

Guess you need to have an incremental number order by Id only and reset it once any of FirebaseUserId, Latitude, Longitude changes. Built in ROW_NUMBER() won't work this way. You can use a variable to implement such a customized logic.

with cte as (
  select *,
    lag(FirebaseUserId) over w FirebaseUserId_prev,
    lag(Latitude) over w Latitude_prev,
    lag(Longitude) over w Longitude_prev
  from tabledata
  window w as (order by Id asc)
)
select Id, FirebaseUserId, Latitude, Longitude,
  case when FirebaseUserId = FirebaseUserId_prev and Latitude = Latitude_prev and Longitude = Longitude_prev then @rownum := @rownum   1 else @rownum := 1 end rownum
from cte
join ( select @rownum := 0 ) r
order by Id asc

demo

CodePudding user response：

(This may be just part of an answer, but I think it's easier to read than in comments and therefore warrants an answer.)

You've got three partitions (I cannot copy/paste from an image, so I cannot list them for you). So you got three number ranges.

In each partition the number starts with 1 (there seem to be a lot of rows from the result missing in your post though?) and increment by the order given in ORDER BY in the OVER clause (not (necessarily) by the order of the rows in the (final) result, which is probably what you thought?). (BTW, FirebaseUserId in the ORDER BY is useless -- it's the same for all rows in a partition because it's part of the PARTITION BY.)

So yeah, 2267 is in the same partition as 2265 and comes directly after it, so the row_number() is increased by 1 to 35 because it was 34 for 2265.

There is no "reset" because the partitions are the same.

To get a solution for what you actually want, it's best if you edit the question and provide a minimal reproducible example, i.e. the CREATE statements of the tables or other objects involved (paste the text, don't use images, don't link to external sites), INSERT statements for sample data (dito) and the desired result with that sample data in tabular text format.