October 16, 2002 at 9:18 am
I would like to select a subset of rows from a table and assign a sequential number during the output. Ideally, I'd love something as simple as "select identity(int,1,1) as id_col from myTable" but the "identity" function requires an "into" clause and I'd like to find a way to do this without a temp table.
Any thoughts?
October 16, 2002 at 12:49 pm
You could try something like this:
use Northwind
go
select count(*)generated_id,a.ProductID,a.ProductName,a.SupplierID,a.CategoryID,a.QuantityPerUnit,a.UnitPrice,
a.UnitsInStock,a.UnitsOnOrder,a.ReorderLevel,a.Discontinued
from products a , products b
where
cast(100+a.ProductID as char)+a.ProductName+cast(a.SupplierID as char)+
cast(a.CategoryID as char)+a.QuantityPerUnit+cast(a.UnitPrice as char)+
cast(a.UnitsInStock as char)+cast(a.UnitsOnOrder as char)+cast(a.ReorderLevel as char)+
cast(a.discontinued as char)
>=
cast(100+b.ProductID as char)+b.ProductName+cast(b.SupplierID as char)+
cast(b.CategoryID as char)+b.QuantityPerUnit+cast(b.UnitPrice as char)+
cast(b.UnitsInStock as char)+cast(b.UnitsOnOrder as char)+cast(b.ReorderLevel as char)+
cast(b.discontinued as char)
group by
a.productid,a.ProductName,a.SupplierID,a.CategoryID,a.QuantityPerUnit,a.UnitPrice,
a.UnitsInStock,a.UnitsOnOrder,a.ReorderLevel,a.Discontinued
order by
generated_id
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 16, 2002 at 4:23 pm
Or you could insert your data into a table variable, which has the identity column and return the data from that table, the example above will involve a nice table scan.
if you want them in date order then you can use
select (select count(*) from myTable my2 where m1.datecol >= my2.datecol) rowid, my1.*
from myTable my1
That will work if datecol is unqiue
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply