Apologies for the misinformed post. But thanks to all for your input, Mark many thanks, your solution has worked for me.

Hopefully i can have an answer to someones problem one...

Apols, linenum 9, should have group id 4 too. typo 🙁

Hi SQLsmasher,

Many thanks for your help, but the query doesn't return the results i need. The resulting query should be:

linenum, fileline, group_id

1 a, 1

2 b, 1

3 c, 1

4 ------2

5 d,...

Hi Chris Morris,

Many thanks for your help, but the query doesn't return the results i need. The resulting query should be:

linenum, fileline, group_id

1 a, 1

2 b, 1

3 c, 1

4 ------2

5...

Thanks for you response. The design is totally crap, legacy system! SP's the only way to go i think.