Get next row based on current row and conditional ordering
This should get you started:
SELECT *
FROM (
  SELECT Id, Name, LEAD(Id) OVER w AS Next_Id, LEAD(Name) OVER w AS Next_Name
  FROM table1
  WINDOW w AS (ORDER BY CASE WHEN Id &lt; 1000 THEN Id ELSE 1000 END, Name)
) WHERE Id = 6;

Note that the order by is working correctly as it returns Id = 1011 as the next Id.
https://dbfiddle.uk/?rdbms=sqlite_3.27&amp;fiddle=ce891ef6631a142500b59ff2c686e108