September 18, 2002 at 1:51 pm
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?)
September 18, 2002 at 2:38 pm
not really. Can you post some DDL and sample data?
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 19, 2002 at 8:40 am
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
September 19, 2002 at 5:18 pm
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?
September 19, 2002 at 6:47 pm
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