October 7, 2011 at 6:27 am
Hi All,
I am looking for a better way to write a query for the following scenario and this is for SQL Server 2k.
I have a table from which I want to delete all records except the 3 records. That means for every order I have to keep 3 items and delete the other items. From the given table data below, for Order-1, I4 , for Order-3, I4 and I5 must be deleted and the remaining orders must remain there.
I can write a query to loop through each order and delete the items but I am looking for a better way to do it. Any guidance would be appreciated.
Orders table:
OrderID ItemsID
1 I1
1 I2
1 I3
1 I4
2 I1
2 I2
3 I1
3 I2
3 I3
3 I4
3 I5
4 I1
4 I2
4 I3
October 7, 2011 at 6:55 am
I don't understand why you'd ever need such a business requirement.
What are you trying to accomplish exactly (big(ger) picture)?
October 7, 2011 at 7:48 am
I think you can write a query that gets these values from the table.
You could so something like
select orderID, itemID
from Orders o
where ItemID > 'I3'
However if you could have I1, I2, I4 for a particular order, then you could alter that you subquery a "top 3" for each orderID and then return that.
Do you have a table that's a child with the items? You ought not to be looping, but instead joining.
October 10, 2011 at 1:34 am
Hi,
If you look at the table, for every order, you can have many items. So once the order is executed, I want to keep the minimum information (per say 3 items for every order). I want delete all the items for every order by keeping only the latest 3 items.
Thanks
October 10, 2011 at 1:41 am
Steve,
The table what I have given is Items table and there will be one parent table for Orders. I can not simply query something like "> I3". The idea is to delete all items which are older than X days but keep at least 3 items.
So for example, for Order-1, there are 5 items which are older than 1 month and 2 items older than 1week. So if my X=10 days, then I have to keep 3 items and delete the remaining 4.
I hope it makes it clear now.
October 10, 2011 at 2:24 am
I've had to make some assumptions due to your lack of DDL in a readily consumable script.
--First, lets create some sample data to play with
IF object_id('tempdb..#OrderItem') IS NOT NULL
BEGIN
DROP TABLE #OrderItem
END
CREATE TABLE #OrderItem (OrderItemID INT IDENTITY NOT NULL PRIMARY KEY, OrderID INT NOT NULL, ItemsID CHAR(2) NOT NULL, FunDate DATETIME NOT NULL)
INSERT INTO #OrderItem (OrderID, ItemsID, FunDate)
SELECT 1, 'I1', DATEADD(DAY,(ABS(CAST(NEWID() AS BINARY(6)) %1000) + 1)*(-1),GETDATE()) --This DATEADD function creates a random date
UNION ALL SELECT 1, 'I2', DATEADD(DAY,(ABS(CAST(NEWID() AS BINARY(6)) %1000) + 1)*(-1),GETDATE())
UNION ALL SELECT 1, 'I3', DATEADD(DAY,(ABS(CAST(NEWID() AS BINARY(6)) %1000) + 1)*(-1),GETDATE())
UNION ALL SELECT 1, 'I4', DATEADD(DAY,(ABS(CAST(NEWID() AS BINARY(6)) %1000) + 1)*(-1),GETDATE())
UNION ALL SELECT 2, 'I1', DATEADD(DAY,(ABS(CAST(NEWID() AS BINARY(6)) %1000) + 1)*(-1),GETDATE())
UNION ALL SELECT 2, 'I2', DATEADD(DAY,(ABS(CAST(NEWID() AS BINARY(6)) %1000) + 1)*(-1),GETDATE())
UNION ALL SELECT 3, 'I1', DATEADD(DAY,(ABS(CAST(NEWID() AS BINARY(6)) %1000) + 1)*(-1),GETDATE())
UNION ALL SELECT 3, 'I2', DATEADD(DAY,(ABS(CAST(NEWID() AS BINARY(6)) %1000) + 1)*(-1),GETDATE())
UNION ALL SELECT 3, 'I3', DATEADD(DAY,(ABS(CAST(NEWID() AS BINARY(6)) %1000) + 1)*(-1),GETDATE())
UNION ALL SELECT 3, 'I4', DATEADD(DAY,(ABS(CAST(NEWID() AS BINARY(6)) %1000) + 1)*(-1),GETDATE())
UNION ALL SELECT 3, 'I5', DATEADD(DAY,(ABS(CAST(NEWID() AS BINARY(6)) %1000) + 1)*(-1),GETDATE())
UNION ALL SELECT 4, 'I1', DATEADD(DAY,(ABS(CAST(NEWID() AS BINARY(6)) %1000) + 1)*(-1),GETDATE())
UNION ALL SELECT 4, 'I2', DATEADD(DAY,(ABS(CAST(NEWID() AS BINARY(6)) %1000) + 1)*(-1),GETDATE())
UNION ALL SELECT 4, 'I3', DATEADD(DAY,(ABS(CAST(NEWID() AS BINARY(6)) %1000) + 1)*(-1),GETDATE())
--Lets take a look at the sample data
SELECT * FROM #OrderItem
--OK, now that we've created sample data, lets look at the query needed to create the results
DELETE FROM #OrderItem
WHERE OrderItemID IN (SELECT OrderItemID
FROM #OrderItem
WHERE ItemsID NOT IN (SELECT TOP 3 ItemsID
FROM #OrderItem b
WHERE OrderID = b.OrderID
ORDER BY b.FunDate))
--Finally, lets look to see if we're left with only 3 records per OrderID
SELECT * FROM #OrderItem
October 11, 2011 at 4:09 am
Hi Cadavre,
That's exactly what I was looking for. I was occupied and hence no time to reply sooner. Thanks.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply