1

I have a Replication Set that we use in Merge Replication(SQL 2005). I am trying to figure out how to apply a Filter of Top 2 to a table.

Also, is there a way to set up a Filter so that it trickles down to sub-tables or would I need to handle that differently?

What I am looking at is a Database of Plans that I am Replicating to our users' laptops. I would like to only Replicate the last two Plans and only replicate the associated data for those two plans. Example, there is a Medication table that lists all the Medication associated to a certain Plan. I would only want to replicate the rows on the Medication table that are tied to one of the Top 2 plans.

I am not sure how I should go about this. I see the Filter option in the Publication Properties but it is by table and I don't seem to have the option of Top 2.

Dennis Williamson
  • 62,149
  • 16
  • 116
  • 151
Refracted Paladin
  • 345
  • 7
  • 20

1 Answers1

1

Filtering can only be done via the WHERE clause. What you would need to put into the filter is something like this.

WHERE PlanId IN (SELECT TOP (2) PlanId FROM Plan ORDER BY CreateDate DESC)

If you put that into the filter for each table it will only replicate the rows for those 2 plans.

To use multiple columns for the filter, something like this "should" work. I haven't tested this, so it might take some tweaking to make it happen.

WHERE PlanId IN (   
        SELECT PlanId FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY ClientId, PlanId ORDER BY CreateDate DESC) RowId,
            PlanId, ClientId
            FROM [Plan] a
            where [Plan].PlanId = a.PlanId
                and [Plan].ClientId = a.ClientID
            ) b
        WHERE b.RowId IN (1,2)
        )
mrdenny
  • 27,174
  • 4
  • 41
  • 69
  • Sweet, close! I just need to rework this to be the TOP 2 Plans for each ClientID(a coulumn in tblPlan). Sometimes I think you may be the only human in the WORLD that knows anything about Replication. I believe you have been the only one to answer every single one of my repl questions. Thank you, thank you. – Refracted Paladin Feb 02 '10 at 20:16
  • Can you reference a view in the WHERE of a filter? – Refracted Paladin Feb 02 '10 at 20:20
  • Yes you can reference a view in a WHERE clause. To do top to for each customer you'll need to use the ROW_NUMBER function. I'll update my answer. – mrdenny Feb 06 '10 at 02:37