Prev: Business Intelligence on Mobile devices
Next: Distinct Column Level Output
From: phdate on 12 Dec 2009 11:18
Erland Sommarskog wrote:
> Say that you need to insert a number of rows in a table, with each
> row being assigned a unique, sequential, id, the typical construct is:
> ...


> Whether that fits in the context you are working, I don't know.
> Furthermore, this solution requires SQL 2005 or higher.
>


Unfortunately not. I use this technique elsewhere but what I need is
the ability to generate such numbers one at a time. And sometimes, I
need 2 or 3 of these at a time, depending on the results of the formulas
I use in the package.
From: phdate on 12 Dec 2009 11:26
Plamen Ratchev wrote:
> Here is another example:
> http://www.sqlmag.com/Article/ArticleID/101339/sql_server_101339.html
>


Thanks but beyond the details of the author's specific case, I think it
is identical to the one I posted.
From: Erland Sommarskog on 12 Dec 2009 15:36
phdate (drscrypt(a)gmail.com) writes:
> Unfortunately not. I use this technique elsewhere but what I need is
> the ability to generate such numbers one at a time. And sometimes, I
> need 2 or 3 of these at a time, depending on the results of the formulas
> I use in the package.

Instead of row_number, you can use a correlated subquery with COUNT(*):

SELECT O.OrderID, O.CustomerID, O.OrderDate,
(SELECT COUNT(*)
FROM Orders O2
WHERE O2.CustomerID = O.CustomerID
AND O2.OrderID <= O.OrderID) AS OrdnoForCustomer
FROM Orders O
ORDER BY O.CustomerID, O.OrderID

The performance for larger result sets is awful, but for 2-3 it should
not be a problem.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

First  |  Prev  | 
Pages: 1 2
Prev: Business Intelligence on Mobile devices
Next: Distinct Column Level Output