SQL Server query order by sequence serie

I am writing a query and I want it to do a order by a series. The first seven records should be ordered by 1,2,3,4,5,6 and 7. And then it should start all over.

I have tried over partition, last_value but I cant figure it out.

This is the SQL code:

set language swedish;

select 
    tblridgruppevent.id, 
    datepart(dw,date) as daynumber, 
    tblRidgrupper.name
from 
    tblRidgruppEvent 
join 
    tblRidgrupper on tblRidgrupper.id = tblRidgruppEvent.ridgruppid
where 
    ridgruppid in (select id from tblRidgrupper 
                   where corporationID = 309 and Removeddate is null)
    and tblridgruppevent.terminID  = (select id from tblTermin 
                                      where corporationID = 309 and removedDate is null and isActive = 1) 
    and tblridgrupper.removeddate is null 
order by  
    datepart(dw, date) 

and this is a example the result:

5887    1   J2
5916    1   J5
6555    2   Junior nybörjare
6004    2   Morgonridning 
5911    3   J2
6467    3   J5

and this is what I would expect:

5887    1   J2
6555    2   Junior nybörjare
5911    3   J2
5916    1   J5
6004    2   Morgonridning
6467    3   J5

Answer

You might get some value by zooming out a little further and consider what you’re trying to do and how else you might do it. SQL tends to perform very poorly with row by row processing as well as operations where a row borrows details from the row before it. You also could run into problems if you need to change what range you repeat at (switching from 7 to 10 or 4 etc).

If you need a number there somewhat arbitrarily still, you could add ROW_NUMBER combined with a modulo to get a repeating increment, then add it to your select/where criteria. It would look something like this:

((ROW_NUMBER() OVER(ORDER BY column ASC) -1) % 7) + 1 AS Number

The outer +1 is to display the results as 1-7 instead of 0-6, and the inner -1 deals with the off by one issue (the column starting at 2 instead of 1). I feel like there’s a better way to deal with that, but it’s not coming to me at the moment.

edit: Looking over your post again, it looks like you’re dealing with days of the week. You can order by Date even if it’s not shown in the select statement, that might be all you need to get this working.