• I am posting this as A solution, not as THE solution. I am hoping to learn from a critique of the answer as much as you are!

    Deleting is easy. The problem is knowing which one to delete. This code will delete the largest AppID.

    To delete:

    with cte as

    (

    Select AppID, Breed,

    Row_Number() over(Partition by breed order by AppID) RowNum

    from #TempCanine

    )

    Delete from cte where RowNum > 5

    Inserting, as you discovered, is harder. A big problem with Inserts is that we do not know the age of the dog. Without something to tell us what it is, I put it at 0. The other problem is how to enter an unknown number of rows. This is where the tally table is very valuable. The important part of the tally table is that by joining against it and using a correct where clause, it will return the number of rows that need to be inserted. As a side note, there are a number of ways to generate a tally table. What I have below is to keep the solution cleaner without cluttering it with a dynamic one. The last issue is generating an ascending appID regardless of the breed.

    create table #tally

    (

    n int

    )

    insert #tally (n) values (1), (2), (3), (4), (5), (6)

    with cte as

    (

    Select count(*) BreedCnt, Breed

    from #TempCanine

    group by breed

    ),

    MaxID as

    (

    Select 1 i, MAX(AppID) AppID from #tempCanine

    )

    insert #TempCanine (breed, age, AppID)

    select c.breed, 0, m.AppID + row_Number() over(order by (select null))

    from #tally t

    inner join maxID m on m.i = 1

    cross apply

    (Select Breed, BreedCnt

    from cte) c

    where t.N > c.BreedCnt and t.N < 6

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/