Link to home
Start Free TrialLog in
Avatar of TheUndecider
TheUndeciderFlag for United States of America

asked on

Sequential number grouped by Ordernumber

Hello,

I'm trying to generate a column in a SQL Server 2008 query  where I can have a sequential number for all the order records that include their order details.  I basically want to have a sequential number grouped by order number, which can be repeated if the order has multiple rows.

In this case, the first row is what I need.


MySq      Ord#      Product            OrdDetailSeq
1      42825      widget1            1
2      42828      widget3            1
3      42829      widget1            1
3      42829      widget2            2
3      42829      widget4            3
4      42832      widget2            1
4      42832      widget3            2
5      42837      widget2            1


Does anyone know how to accomplish this?

Thanks.
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Not sure what 'In this case, the first row is what I need.' means, but looking at the desired set and the rest of the description OrdDetailSeq can be generated using ROW_NUMBER:  
SELECT MySq, [Ord#], Product, 
  ROW_NUMBER() OVER (PARTITION BY [Ord#] ORDER BY Product) as OrdDetailSeq
FROM YourTable

Open in new window

Avatar of TheUndecider

ASKER

Thank you for replying, but what I need is the first column: MySq.  The rest of the columns are already there.  I need the sequential number for each set of rows that share the same order number.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the split.  Good luck with your project.  -Jim