Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nbmbm
Starting Member
2 Posts |
 Posted - 2005-12-05 : 16:29:59
|
| Hi All,We have a list of Restaurants in database - which users can search by zipcode.We were giving the listings free for a year. Now that the year is up - we want to delete unpaid listings. But there is a constraint - we want to delete only those listing - if they are 1.5 years old and if there are more than 3 listings in that zipcode in our database. So basically we need to keep a minimum of 3 listings rom each zipcodes - irrespective of whether paid or not. So if a zipcode has 5 listings - all unpaid, we need to remove the oldest 2 - but keep rest 3.Here's a basic SQL query to get listings from zipcodes with more than 3 listings. Now need help in deleting as per logic explained above. Any help would be greatly appreciated.SELECT *FROM ListingTableWHERE (ZipCode IN (SELECT ZipCode FROM ListingTable GROUP BY ZipCode HAVING (COUNT(*) > 3))) AND (Status = 'unpaid') AND (EntryDate < '01/07/04')Order by ZipCode , EntryDate |
|
|
jbolduc81
Starting Member
6 Posts |
Posted - 2005-12-05 : 16:44:40
|
| Do you want to make all this work in the same query ? because you could declare a cursor on the resultset of this query and move in it while incrementing a counter, deleting the row when it is higher then 3, reseting it when the zipcode changes. |
 |
|
|
nbmbm
Starting Member
2 Posts |
Posted - 2005-12-05 : 16:54:36
|
| Any solution will work for me. I have not much experience with cursors. Any help (with sample query) would greatly help.Thanks |
|
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-12-05 : 18:53:38
|
| Not sure how efficient this is for a large table, but you can give this a shot:DELETE t3 FROM ListingTable AS t3 WHERE NOT EXISTS (SELECT 1 FROM ListingTable AS t1 WHERE (SELECT COUNT(DISTINCT t2.entrydate) FROM ListingTable AS t2 WHERE t2.zipcode = t1.zipcode AND t2.entrydate >= t1.entrydate) <= 3 AND t1.zipcode = t3.zipcode and t1.status = t3.status and t1.entrydate = t3.entrydate) AND t3.entrydate < DATEADD(m, -18, getdate()) Of course, change the "DELETE" to a SELECT statement first to make sure it gets the correct records.Also, for this part:(SELECT COUNT(DISTINCT t2.entrydate) FROM ListingTable AS t2 WHERE t2.zipcode = t1.zipcode AND t2.entrydate >= t1.entrydate)substitute "entrydate" with whatever column that actually makes unique records within each zipcode - I just used entrydate because I don't know what other column you have. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Deleting records