sql - SQL - Ordering by a number which recycles when it reaches 999

Question

I'm trying to order by a number which recycles when it reaches 999. The number range runs 1 - 999 and it should always run in ascending order. Currently my SQL (ORDER BY BoxID ASC, Seqno ASC) is generating the following result:

The box number will always increment by 1 every 9 sequence numbers. so in the example shown in the box 35897 the range should run 999, 1, 2, 3, 4, 5, 6, 7, 8

There are no time stamps which can be used to find the order as the sequential numbers are inserted into the database in a random order.

标签： sqlsql-server

Answer 1

This is a bit of a stab in the dark, but perhaps...

SELECT Box,
       Number
FROM YourTable
ORDER BY Box,
         ROW_NUMBER() OVER (PARTITION BY Number ORDER BY Box ASC),
         Number;

This, however, won't work if a single box has over 999 values for Number.

DB<>Fiddle showing solution (As DB Fiddle limits the result set to 10 rows, I have had to use a CTE to show relevant rows).