Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
slb5w
Starting Member
3 Posts |
 Posted - 2005-11-22 : 18:18:49
|
| Hi all,Have a table with approx a million rows, and would like to avoid cursors. The problem can be illustrated in the Northwind database. In the Orders Details table, I would need to create a 'line number' starting from 1 and incrementing by 1 until the next change in orderID.Results to look like this:OrderID line_no ProductID UnitPrice ----------- ------- ----------- ------------10248 1 11 14.0000 10248 2 42 9.8000 10248 3 72 34.8000 10249 1 14 18.6000 10249 2 51 42.4000 10250 1 41 7.7000 10250 2 51 42.4000 10250 3 65 16.8000 10251 1 22 16.8000 10251 2 57 15.6000 10251 3 65 16.8000 Please advise on the best query to achieve this on such a large table. Note that the table will be ordered by an identity column.Many Thanks |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-11-22 : 20:35:22
|
| Think there is a thread or article on streaks somewhere.An easy way but not very efficient isselect *, seq = (select count(*) from tbl t2 where t.grp = t2.grp and t2.id <= t.id)from tbl torder by grp, seq==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-11-22 : 22:10:08
|
| You can also do this:SELECT A.OrderId, COUNT(*) line_no,A.ProductId, A.UnitPrice FROM [order details] A JOIN [order details] bON A.orderid = B.orderid AND A.ProductId >= B.ProductId GROUP BY a.OrderId, A.ProductId, A.UnitPrice ORDER BY a.OrderId, A.ProductId, A.UnitPrice This assumes that OrderId + ProductId creates a unique key. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-23 : 00:47:34
|
| >>Have a table with approx a million rows, Where do you want to show the data?The suggested methods will take more timeIf you use Reports you can make use of Running Total Feature with count and reset it in every groupMadhivananFailing to plan is Planning to fail |
|
|
|
slb5w
Starting Member
3 Posts |
Posted - 2005-11-23 : 07:26:31
|
| Hi,Thanks for the posts. They work very efficently on the million plus table once indexs are up.Madhivanan, the work needs to be done before reporting tools hit the data.BTW, I think SQL 2005 has a builtin TSQL function to solve this? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-23 : 07:53:13
|
| >>Madhivanan, the work needs to be done before reporting tools hit the data.Why?If you show the data at Reports, then why cant you use its feature?Which reporting tool are you using?MadhivananFailing to plan is Planning to fail |
|
|
|
slb5w
Starting Member
3 Posts |
Posted - 2005-11-23 : 10:14:48
|
| data duplicated by source system (unavoidable), data contains no line no's, therefore impossible to not exculde genuine duplicates using DISTINCT. Plus data needs to be cleaned before datawarehousing.hope this answers you, Madhivanan. |
|
|
|
|
|
|
|
|
Best way to achieve this result