Clever Geek Handbook

Get data from SQLServer in ASC order

I have a table with a column name and value. Although the data is stored in the sql server database, it sorts itself randomly i.e. id value 1,2,3,4,5,6,7,14,15,16,17,8,9,10 and similar.

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

those.

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

I tried request

Group 1: select the 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 ASC ID

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

What is my problem, since the data is sorted randomly, when storing it in the database, it is retrieved randomly.

Below is a screenshot of how my data is being saved to the database.

Screenshot help me select data like the above group.

+3


source to share


4 answers


Use and , not . OFFSET

FETCH

TOP

eg. The second group will be:

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


End playback 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
+4


source


This will also give the desired results. For other queries, change 33 with different values, now it gets values ​​from 33 to 22.



WITH t AS
( SELECT ROW_NUMBER() OVER (ORDER BY id) AS row_num, *
    FROM tblCode )
SELECT TOP 11 *
FROM t
WHERE row_num > 33
0


source


Try it,

select * from Table Name Order by ID
0


source


Hope I don't understand:

--Group 1
SELECT *
FROM tblCode
WHERE id >= 1
    AND id <= 11
ORDER BY id ASC

--Group 2
SELECT *
FROM tblCode
WHERE id >= 12
    AND id <= 22
ORDER BY id ASC

--Group 3
SELECT *
FROM tblCode
WHERE id >= 23
    AND id <= 33
ORDER BY id ASC

You can also store the increments of the variable. Perhaps something like this (i.e.) you are sending parameter group # 3:

--Group 3
SELECT @Group = 3 --just for sample, param should sent From application 
SELECT @lastIndex =  3*11
SELECT @indexStart = @lastIndex - 10
SELECT *
FROM tblCode
WHERE id >= @indexStart
    AND id <= @lastIndex
ORDER BY id ASC
0


source






More articles:


All Articles