Add a column to each record with a number increment?

Hi All,
I need to know if it is possible to add a column to my query that will
represent a number for each record?
(e.g...)
[Record#], [Last Name], [First Name]
0 , Miller , John
1 , Brady , Michael
2 , Smith , Wendy
etc...
I need to do something like this:
DECLARE @.RN Int
SET @.RN = 0
SELECT (@.RN = @.RN + 1) AS [Record#],
LastName As [Last Name],
FirstName As [First Name]
FROM Table1
Order By [Record#]
Any help would be greatly appreciated,
John.Hi,
Consider using autonumber in Access or an Identity field in MS SQL
Mike -- FrontPage MVP '97 - '02
http://www.websunlimited.com
FrontPage Add-ins Summer Promotion -
FREE Purchase J-Bots Plus 2004 & receive Meta Tag Maker 2004 a $24.95 value
FREE
"Thunder" <jrugo@.patmedia.net> wrote in message news:OorqlyBqFHA.904@.TK2MSFTNGP10.phx.gbl..
.
> Hi All,
> I need to know if it is possible to add a column to my query that will rep
resent a number for each record?
> (e.g...)
> [Record#], [Last Name], [First Name]
> 0 , Miller , John
> 1 , Brady , Michael
> 2 , Smith , Wendy
> etc...
> I need to do something like this:
> DECLARE @.RN Int
> SET @.RN = 0
> SELECT (@.RN = @.RN + 1) AS [Record#],
> LastName As [Last Name],
> FirstName As [First Name]
> FROM Table1
> Order By [Record#]
> Any help would be greatly appreciated,
> John.
>|||How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"Thunder" wrote:

> Hi All,
> I need to know if it is possible to add a column to my query that will
> represent a number for each record?
> (e.g...)
> [Record#], [Last Name], [First Name]
> 0 , Miller , John
> 1 , Brady , Michael
> 2 , Smith , Wendy
> etc...
> I need to do something like this:
> DECLARE @.RN Int
> SET @.RN = 0
> SELECT (@.RN = @.RN + 1) AS [Record#],
> LastName As [Last Name],
> FirstName As [First Name]
> FROM Table1
> Order By [Record#]
> Any help would be greatly appreciated,
> John.
>
>|||Thanks for the ideas; but I should have qualified my need.
I am using a LEFT OUTER JOIN to pull data back.
So what happens is that I can have sever records from table2 creating
several rows with Table1's data. I wanted to number them so that it is
easier for the user to see the groupings. Does that make sense?
Table1 has Requisitions (one req per Row)
Table2 has the associated PO info. There can be several PO's related to one
Req.
[Record#], Req#, PONumber
1,111, PO123
1,111, PO456
2,222, POabc
2,222, POefg
2,222, POhij
etc...
Thanks so much,
John.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:A365DB6D-7AFC-43E8-A9E5-630805308C3D@.microsoft.com...
> How to dynamically number rows in a SELECT Statement
> http://support.microsoft.com/defaul...kb;en-us;186133
>
> AMB
> "Thunder" wrote:
>|||http://support.microsoft.com/defaul...B;EN-US;q186133
"Thunder" <jrugo@.patmedia.net> wrote in message
news:OorqlyBqFHA.904@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to know if it is possible to add a column to my query that will
> represent a number for each record?
> (e.g...)
> [Record#], [Last Name], [First Name]
> 0 , Miller , John
> 1 , Brady , Michael
> 2 , Smith , Wendy
> etc...
> I need to do something like this:
> DECLARE @.RN Int
> SET @.RN = 0
> SELECT (@.RN = @.RN + 1) AS [Record#],
> LastName As [Last Name],
> FirstName As [First Name]
> FROM Table1
> Order By [Record#]
> Any help would be greatly appreciated,
> John.
>|||Try,
select
(select count(*) from t1 as a where a.reqid <= t1.reqid) as rank,
t1.reqid,
t2.ponumber,
..
from t1 left join t2 on t1.reqid = t2.reqid
go
AMB
"Thunder" wrote:

> Thanks for the ideas; but I should have qualified my need.
> I am using a LEFT OUTER JOIN to pull data back.
> So what happens is that I can have sever records from table2 creating
> several rows with Table1's data. I wanted to number them so that it is
> easier for the user to see the groupings. Does that make sense?
> Table1 has Requisitions (one req per Row)
> Table2 has the associated PO info. There can be several PO's related to o
ne
> Req.
> [Record#], Req#, PONumber
> 1,111, PO123
> 1,111, PO456
> 2,222, POabc
> 2,222, POefg
> 2,222, POhij
> etc...
> Thanks so much,
> John.
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:A365DB6D-7AFC-43E8-A9E5-630805308C3D@.microsoft.com...
>
>|||http://www.aspfaq.com/2427
"Thunder" <jrugo@.patmedia.net> wrote in message
news:OorqlyBqFHA.904@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to know if it is possible to add a column to my query that will
> represent a number for each record?
> (e.g...)
> [Record#], [Last Name], [First Name]
> 0 , Miller , John
> 1 , Brady , Michael
> 2 , Smith , Wendy
> etc...
> I need to do something like this:
> DECLARE @.RN Int
> SET @.RN = 0
> SELECT (@.RN = @.RN + 1) AS [Record#],
> LastName As [Last Name],
> FirstName As [First Name]
> FROM Table1
> Order By [Record#]
> Any help would be greatly appreciated,
> John.
>|||if the order of the records doesn't matter, then you can use
SELECT IDENTITY(INT, 0, 1) AS [Record#], [Last Name], [First Name] INTO #t1
FROM Table1
SELECT [Record#], [Last Name], [First Name] FROM #t1 ORDER BY [Record#]
if the order of the records does matter, then you can use:
DECLARE @.t1 TABLE ([Record#] INT IDENTITY(0, 1) NOT NULL, [Last Name]
VARCHAR(25) NOT NULL, [First name] VARCHAR(25) NOT NULL)
INSERT @.t1 ([Last Name], [First Name])
SELECT [Last Name], [First Name] FROM Table1 ORDER BY [Last Name],
[First Name]
SELECT [Record#], [Last Name], [First Name] FROM @.t1 ORDER BY [Record#]
Note: you must use the second query if the order is important, becuase the
identity values are not guaranteed to be in any specific order if you use
the IDENTITY function of SELECT INTO because the ORDER BY clause is executed
after the IDENTITY values are assigned. You must separate the insert from
the select in order to guarantee a specific order. Assume that an order by
existed in the first example: the select is executed, the identity values
are assigned, the results are sorted, and then the rows are inserted. In
the second example, the select is executed, the results are sorted, the
identity values are assigned, and then the rows are inserted.
"Thunder" <jrugo@.patmedia.net> wrote in message
news:OorqlyBqFHA.904@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to know if it is possible to add a column to my query that will
> represent a number for each record?
> (e.g...)
> [Record#], [Last Name], [First Name]
> 0 , Miller , John
> 1 , Brady , Michael
> 2 , Smith , Wendy
> etc...
> I need to do something like this:
> DECLARE @.RN Int
> SET @.RN = 0
> SELECT (@.RN = @.RN + 1) AS [Record#],
> LastName As [Last Name],
> FirstName As [First Name]
> FROM Table1
> Order By [Record#]
> Any help would be greatly appreciated,
> John.
>|||Thank you to all,
This worked Perfectly, Thank You :)
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3F9E9BA5-1F29-460B-9848-A973968EAFB1@.microsoft.com...
> Try,
> select
> (select count(*) from t1 as a where a.reqid <= t1.reqid) as rank,
> t1.reqid,
> t2.ponumber,
> ...
> from t1 left join t2 on t1.reqid = t2.reqid
> go
>
> AMB
> "Thunder" wrote:
>