Okay folks,
I'm sure this can be done - and I could do it if I were cleverer! I'm looking to number records in a (local/temp) table but that at the change of the constituent number, restart the numbering at 1. So, it'd be similar to the row_number() function. A table (or records in a select statement would look like like the following - it's the ability to create the record_no field I'm interested in.
Customer_no
Event
Record_no
Row_no
40661323
ACO 2006 Launch
1
ACO 2007 Launch
2
ACO 2008 Launch
3
ACO 2009 Launch
4
50123456
ACO 2005 Launch
5
6
7
If anyone knows how to do this and would spread the joy over my way - I'd be very grateful!
Martin
Hi Martin
You can do this with the row_number command just need to use it with over and partition by and order by.
eg. row_number()over(partition by customer_no order by customer_no,event)
Hope that helps
Mark
Mark,
THANK YOU! Works like a dream. I knew it had to be possible, if only some kind soul was prepared to share the syntax!