Add in auto number in the query result

How can I add in the auto-increment field in my query result?
E.g
Select * from tmp1
The result from this transact sql is
VendorName VendorPhone
Ven1 123456
Ven2 789654
How can I modified my query to return result as below:( auto number in front
)
VendorID VendorName VendorPhone
1 Ven1 123456
2 Ven2 789654
Thanks lot if anyone can provide me the solutions cos I am using temporary
table in VBA. I am wondering whether I can do it in sql itself or not.
ThanksOne way is to declare a Table Variable to hold the PKs of the rows that will
be in the Final Output.. but when you declar ethe table, also include a
Identity column...
Declare @.OutPKs Table
(RowID Integer Identity Primary Key Not Null,
PkID Integer Not Null)
Then insert all the PKS into this table based on the exact same where clause
and Order By that you would use in your previous query...
Insert @.OutPKs (PkID)
Select PKID
From <Real Data Tables>
Where <Filter conditions>
Order By <Sort Conditions>
Then, finally, output the data you actually want by joining the real data
tables to this @.OutPks Table, and include the RowID in the output...
Select RowID, PkID, <Other Columns>
From @.OutPks P Join <Other Tables>
Order By RowID
"Sql Fren" wrote:

> How can I add in the auto-increment field in my query result?
> E.g
> Select * from tmp1
> The result from this transact sql is
> VendorName VendorPhone
> Ven1 123456
> Ven2 789654
> How can I modified my query to return result as below:( auto number in fro
nt)
> VendorID VendorName VendorPhone
> 1 Ven1 123456
> 2 Ven2 789654
>
> Thanks lot if anyone can provide me the solutions cos I am using temporary
> table in VBA. I am wondering whether I can do it in sql itself or not.
> Thanks
>|||Another way is to include an additional column which is the output of a
correlated subquery, which counts the number of records in the same set whic
h
have a columnvalue less than or equal to the columnvalue in the row the
subquery is running on... The columnvalue must be the column the output is
sorting on, and the columnvalues must be unique..
Say the column you're using is OrderDate...
Select (Select Count(*) From Table
Where OrderDate <= O.OrderDate) as RowNum,
<Other Columns>
From Table O
Order By OrderDate
If the outer query has a where clause, filtering the rows which will be
produced, the subquery must include the same where clause, or the count will
be wrong...
"Sql Fren" wrote:

> How can I add in the auto-increment field in my query result?
> E.g
> Select * from tmp1
> The result from this transact sql is
> VendorName VendorPhone
> Ven1 123456
> Ven2 789654
> How can I modified my query to return result as below:( auto number in fro
nt)
> VendorID VendorName VendorPhone
> 1 Ven1 123456
> 2 Ven2 789654
>
> Thanks lot if anyone can provide me the solutions cos I am using temporary
> table in VBA. I am wondering whether I can do it in sql itself or not.
> Thanks
>|||Much better to add this fake "rank" number in VBA since, presumably, you are
going to be looping through each row anyway, and VBA is pretty good at
simple math (rowNum = rowNum + 1). It is not very efficient at all to do
this at the database level, but of course, there are kludges.
http://www.aspfaq.com/2427
On 3/16/05 8:39 PM, in article
39D05AEC-CB60-40A3-A140-583EBDC58EA9@.microsoft.com, "Sql Fren"
<SqlFren@.discussions.microsoft.com> wrote:

> How can I add in the auto-increment field in my query result?
> E.g
> Select * from tmp1
> The result from this transact sql is
> VendorName VendorPhone
> Ven1 123456
> Ven2 789654
> How can I modified my query to return result as below:( auto number in fro
nt)
> VendorID VendorName VendorPhone
> 1 Ven1 123456
> 2 Ven2 789654
>
> Thanks lot if anyone can provide me the solutions cos I am using temporary
> table in VBA. I am wondering whether I can do it in sql itself or not.
> Thanks
>