March 5, 2015 at 4:19 am
hi all, whit this query i get only the records i need, but i would like to output in this way
1 - 20
21 - 30
31 - 40
of course in the real environment the ID are not consecutive, this is just one example of data.
declare @temp table (ID int)
declare @i int = 1
while(@i<1000) begin
insert into @temp values (@i)
set @i=@i+1
end
select ID from (
select ID, row_number() over (order by ID)
as rn
from @temp
) q where (rn % 20=0) OR (rn % 20=1)
Thanks.
March 5, 2015 at 4:43 am
i did it
declare @temp table (ID int)
declare @i int = 1
while(@i<1000) begin
insert into @temp values (@i)
set @i=@i+1
end
Select cast(A.ID as varchar) + ' - ' + cast(B.ID as varchar) From
(
select ID, rn + 19 as rn from (
select ID, row_number() over (order by ID)
as rn
from @temp
) q where (rn % 20=1)
) A Inner Join
(
select ID, rn from (
select ID, row_number() over (order by ID)
as rn
from @temp
) q where (rn % 20=0)
) B on A.rn=B.rn
March 5, 2015 at 4:45 am
Does my version work?
declare @temp table (ID int);
declare @i int = 1;
while (@i < 1000)
begin
insert into @temp
values (@i);
set @i = @i + 1;
end;
with AllRows
as (select dta = cast(ID as varchar(10)) + ' - ' + cast(lead(q.ID, 1, null) over (order by ID) as varchar(10))
,ID
from (select ID
,row_number() over (order by ID) as rn
,IDx = cast(ID as varchar(10))
from @temp
) q
where (rn % 20 = 0)
or (rn % 20 = 1)
)
select dta
from AllRows
where ID % 20 = 1
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 5, 2015 at 6:34 am
yes it does, thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply