SQL that generates a sequence

Thanks Lawrence, but that is not what I need.
I need single query Select statement. On Oracle it looks like this:
SELECT LEVEL FROM DUAL

CONNECT BY LEVEL <= 100



"Lawrence Garvin" wrote in message

> "Yuri" wrote in message
>
>> Hello,
>> I need to create a query that will return a sequence of numbers,
>> something
>> like this:
>> 1
>> 2
>> 3
>> ...
>> 99
>> 100
>>
>> Please help
>> Thanks,
>> Yuri
>
>
> declare @i int
> declare @maxvalue int
>
> set @i = 1
> set @maxvalue = 100
>
> while @i <= @maxvalue
> begin
> select @i
> set @i = @i + 1
> end
>
>
>
>
> --
> Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
> Principal/CTO, Onsite Technology Solutions, Houston, Texas
 >> Stay informed about: SQL that generates a sequence 

"Yuri Den" wrote in message


>>> Hello,
>>> I need to create a query that will return a sequence of numbers,
>>> something
>>> like this:
>>> 1
>>> 2
>>> 3
>>> ...
>>> 99
>>> 100


>> declare @i int
>> declare @maxvalue int
>>
>> set @i = 1
>> set @maxvalue = 100
>>
>> while @i <= @maxvalue
>> begin
>> select @i
>> set @i = @i + 1
>> end


> Thanks Lawrence, but that is not what I need.
> I need single query Select statement. On Oracle it looks like this:

> SELECT LEVEL FROM DUAL
> CONNECT BY LEVEL <= 100

Yeah.. well, this ain't Oracle, and you got an answer befitting your
question.

Your query won't provide anything unless there's a table populated with
data.

Now, if you'd like to provide *additional* constraints to your question...
Like, maybe, the data is already in a table and you're trying to retreive
data from the table???

Then maybe something as basic as
SELECT <column> FROM <table> WHERE <column> <= 100 ORDER BY <column>

But then... if we're asking questions this simple,
your better source for help is probably a book named something like
"Teach Yourself T-SQL in Seven Days".

Do you have a real *problem* to solve, or do you just not know anything
about T-SQL?


--
Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
Principal/CTO, Onsite Technology Solutions, Houston, Texas
 >> Stay informed about: SQL that generates a sequence 

Lawrence, I love your answer! What an elegant solution
SELECT <column> FROM <table> WHERE <column> <= 100 ORDER BY <column>

In case if someone else encountered with similar problem, here is link to
solution:
http://www.projectdmx.com:80/tsql/tblnumbers.aspx

Thanks,
Yuri


"Lawrence Garvin" wrote in message

> "Yuri Den" wrote in message
>
>
>>>> Hello,
>>>> I need to create a query that will return a sequence of numbers,
>>>> something
>>>> like this:
>>>> 1
>>>> 2
>>>> 3
>>>> ...
>>>> 99
>>>> 100
>
>
>>> declare @i int
>>> declare @maxvalue int
>>>
>>> set @i = 1
>>> set @maxvalue = 100
>>>
>>> while @i <= @maxvalue
>>> begin
>>> select @i
>>> set @i = @i + 1
>>> end
>
>
>> Thanks Lawrence, but that is not what I need.
>> I need single query Select statement. On Oracle it looks like this:
>
>> SELECT LEVEL FROM DUAL
>> CONNECT BY LEVEL <= 100
>
> Yeah.. well, this ain't Oracle, and you got an answer befitting your
> question.
>
> Your query won't provide anything unless there's a table populated with
> data.
>
> Now, if you'd like to provide *additional* constraints to your question...
> Like, maybe, the data is already in a table and you're trying to retreive
> data from the table???
>
> Then maybe something as basic as
> SELECT <column> FROM <table> WHERE <column> <= 100 ORDER BY <column>
>
> But then... if we're asking questions this simple,
> your better source for help is probably a book named something like
> "Teach Yourself T-SQL in Seven Days".
>
> Do you have a real *problem* to solve, or do you just not know anything
> about T-SQL?
>
>
> --
> Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
> Principal/CTO, Onsite Technology Solutions, Houston, Texas
>
 >> Stay informed about: SQL that generates a sequence 

"Yuri Den" wrote in message


> Lawrence, I love your answer! What an elegant solution
> SELECT <column> FROM <table> WHERE <column> <= 100 ORDER BY <column>

<shaking head> I'm glad the solution answered your question. </shaking head>

> In case if someone else encountered with similar problem, here is link to
> solution:
> http://www.projectdmx.com:80/tsql/tblnumbers.aspx

Actually, I think this is the link to the *problem*.

The page at projectdmx.com describes how to *populate* a table with a set of
ordered values.

This solution (and question) describes how to extract a specified set of
values from that table, which I presume the author of the article at
projectdmx.com considered elementary enough not to require explicit
description.

--
Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
Principal/CTO, Onsite Technology Solutions, Houston, Texas
 >> Stay informed about: SQL that generates a sequence 

Lawrence,

I have no idea why you are shaking your head. Common Table Expressions are
exactly what Yuri was looking for. You may need a few more MS certifications
to figure it out.

"Lawrence Garvin" wrote:

> "Yuri Den" wrote in message
>
>
> > Lawrence, I love your answer! What an elegant solution
> > SELECT <column> FROM <table> WHERE <column> <= 100 ORDER BY <column>
>
> <shaking head> I'm glad the solution answered your question. </shaking head>
>
> > In case if someone else encountered with similar problem, here is link to
> > solution:
> > http://www.projectdmx.com:80/tsql/tblnumbers.aspx
>
> Actually, I think this is the link to the *problem*.
>
> The page at projectdmx.com describes how to *populate* a table with a set of
> ordered values.
>
> This solution (and question) describes how to extract a specified set of
> values from that table, which I presume the author of the article at
> projectdmx.com considered elementary enough not to require explicit
> description.
>
> --
> Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
> Principal/CTO, Onsite Technology Solutions, Houston, Texas
>
>
 >> Stay informed about: SQL that generates a sequence