delete rows beyond the first 10?

  • How can I delete all the rows beyond the first 10? And, to make it more interesting, I need to do it with a group by. So, count the number of "Group by (foreign key)", if there are more than 10, delete them. Now do the same for the next foreign key.

    (am I explaining that well?)

  • not really. Can you post some DDL and sample data?

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

  • Ok, let's say I have a table of events, and each has a category. Ok, two categories with 11 events in each, each event one day into the past, with the least recent event being 11 days ago (which makes a table of 22 rows). What I want is a query that saves the first 10 records in each category, and then selects everything else. In this case, it would return 1 record from each category, that would happen to be the eleventh day ago.

    What I've tried already is:

    select *

    from calendar

    where (eventid not in

    (select to 10 eventid

    from calendar

    order by eventdate)

    )

    Obviously, I need to somehow take the category into account using a 'group by' statement, I'm just not sure how.

    Thanks for your patience

  • This might do the trick:SELECT * FROM calendar AS cWHERE c.eventID NOT IN (SELECT TOP 10 eventid FROM calendar AS nc WHERE nc.categoryID=c.categoryID ORDER BY eventDate)Is there a way that you can do the search by date?

  • Well, it still needs to address the issue of 10 rows for the first category (leaving 1), and 10 for the second (leaving 1). Your example leaves 12. I need the inner select to be repeated for each group of events, even if that means eliminating all of them (say there were only 8 rows in a category, none should be left).

    So, let's see.. This seems right, can someone check my logic?

    SELECT c.EventID,c.CategoryID

    FROM svc_Calendar AS c

    GROUP BY c.CategoryID,c.EventID

    HAVING (c.EventID NOT IN

    (SELECT TOP 10 nc.EventID

    FROM svc_Calendar as nc

    WHERE nc.CategoryID=c.CategoryID

    ORDER BY nc.EventDate desc)

    )

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply