You can add this to your select statement if you are using SQL 2005 or higher
row_number() over (order by TableName.FieldName)
declare @tableVar table(
ii int)
declare @i int
set @i = 1
while (@i < 11)
begin
insert into @tableVar values (@i)
set @i = @i + 1
end
select * from @tableVar
with RecursiveCTE (ii)
as
(
select 1 as ii
UNION ALL
select ii + 1 from RecursiveCTE
where ii < 10
)
select * from RecursiveCTE
declare @startpoint int;
declare @endpoint int;
declare @interval int;
set @startpoint = 1;
set @endpoint = 100;
set @interval = 2;
with RecursiveCTE (ii)
as
(
select @startpoint as ii
UNION ALL
select ii + @interval from RecursiveCTE
where ii < @endpoint
)
select * from RecursiveCTE;
select
-- ...
-- th.dg+
h.dg*100+
t.dg*10+
u.dg+1 as number
from
( select 0 as dg union select 1 union select 2 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as u
cross join
( select 0 as dg union select 1 union select 2 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as t
cross join
( select 0 as dg union select 1 union select 2 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as h
-- cross join
-- ( select 0 as dg union select 1 union select 2 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as th
-- cross join
-- ...
order by
number
select
-- ...
-- th.dg*1000+
h.dg*100+
t.dg*10+
u.dg+1 as number
from
( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as u
cross join
( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as t
cross join
( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as h
-- cross join
-- ( select 0 as dg union select 1 union select 2 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as th
-- cross join
-- ...
order by
number
Title | # Comments | Views | Activity |
---|---|---|---|
Help on Setting up an identical test database | 17 | 50 | |
C# Application Local DB Connection String | 23 | 57 | |
Excel Power Query Allow User to Input any Number on Parameter | 2 | 29 | |
When to use an Aggregate Function. | 18 | 33 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
20 Experts available now in Live!