Thanks didn't think of it like that having a separate table could work. Will have a go and let you know how i get on.

It actually just needs to an id column not unique in each row but unique in the combination of dob, source of specimen and organism. As we can get multiple...

I need to assign a unique id as we have an application which handles the data and this is needed to de-duplicate records when running reports another reason why i...

Please use this to create the data table the previous one i sent was a an earlier version which had errors:

IF OBJECT_ID('TempDB..#t','u') IS NOT NULL

...

Hi I need the query changing so that it now looks for records where the time since any date is greater than 14 days where patient id, organism id, antib...

Thanks Greg that worked a treat.

Sorry I made a typo on my original request what I meant to say is I would like to exclude records which are less than 14 days from the first...

Thanks for that i am a newbiw on these forums will remeber for next time, i am using SQL 2000 so ROW_NUMBER wont work is there an equivalent in sql...

What do you mean by o/p?

Thanks guys i will try your suggestions and let you know the outcome.