Get data from SQLServer in ASC order

Question

I have a table with column name id and value. While data is being saved in sql server database, it sorts itself in random order, i.e id value 1,2,3,4,5,6,7,14,15,16,17,8,9,10 and likewise.

I need to retrieve data in 4 groups with each having 11 data in asc id order,

that is,

Group 1: 1-11 Group 2 : 12-22 Group 3 : 23-33 Group 4 : 33-44

I have tried query

Group 1:select top(11) * from tblCode order by id ASC

Group 2:SELECT top(22)* FROM tblCode except select top(11) * from tblCode order by id ASC

Group 3:SELECT top(33)* FROM tblCode except select top(22) * from tblQRCode order by id ASC

group 4:SELECT top(44)* FROM tblCode except select top(33) * from tblCode order by id ASC

What my problem is since data are sorted randomly while saving them into database, they are retrieved randomly.

Below is the screenshot of how my data are saved in database.

help me select data as above mentioned group.

Answer 1

Use OFFSET and FETCH rather than TOP.

E.g. Group two would be:

select *
from tblCode
order by id ASC
offset 11 rows
fetch next 11 rows only

---

Complete repro script:

declare @t table (ID int not null, Value varchar(93) not null);
;With Numbers as (
    select ROW_NUMBER() OVER (ORDER BY so1.object_id) as n
    from sys.objects so1,sys.objects so2,sys.objects so3
)
insert into @t (ID,Value)
select n,'PEC-' + CONVERT(varchar(93),n)
from Numbers
where n between 1 and 1000

select *
from @t
order by id ASC
offset 11 rows
fetch next 11 rows only

Result:

ID          Value
----------- ---------
12          PEC-12
13          PEC-13
14          PEC-14
15          PEC-15
16          PEC-16
17          PEC-17
18          PEC-18
19          PEC-19
20          PEC-20
21          PEC-21
22          PEC-22