I have a table named "policy_details" having four columns called policy_details(varchar),effectdate(datetime),Historyid(int) and policy_status(varchar).
I want to keep only the maximum historyid records in "policy_details" where policy_details and effectdate should be equal and policy_status should be '30240084' . I want to delete the remaining records where policy_details and effectdate should be equal and policy_status should be '30240084'.
Cheers
Praveen
So if you order by policy_details ASC, affectdate ASC, historyid DESC then you only want to keep the first row from each (policy_details, affectdate) group.
I would use the ROW_NUMBER() function and delete where it's not 1
DELETE
FROM policy_details
WHERE ROW_NUMBER() OVER ( PARTITION BY policy_details, affectdate
ORDER BY historyid DESC
) > 1
AND policy_status = '30240084'
|||
Adam,
Thanks for your query.
I am getting an error "The ranking function "ROW_NUMBER" must have an ORDER BY clause."
One more thing is i want same effectdate with only date but not time part.
DELETE
FROM policy_details
WHERE ROW_NUMBER() OVER ( PARTITION BY policy_details,CONVERT(CHAR(10),effectdate,103)
ORDER BY historyid DESC
) > 1
AND policy_status = '30240084'
I did not understand "The ranking function "ROW_NUMBER" must have an ORDER BY clause" error though it is having order by clause. Any ideas?
Cheers
Praveen
|||
Use A CTE, it will work:
WITH myCTE
AS
(SELECT ROW_NUMBER() OVER ( PARTITION BY policy_details,CONVERT(CHAR(10),effectdate,103) ORDER BY historyid DESC) as num
FROM policy_details
WHERE policy_status = '30240084')
DELETE FROM myCTE WHERE num>1
|||
Interesting, when I write the following query, which is almost exactly like yours:
begin transaction
DELETE
FROM person.address
WHERE ROW_NUMBER() OVER ( PARTITION BY addressId
ORDER BY addressLine1 DESC
) > 1
and addressLine1 = 'fred'
rollback transaction
The error I get is:
Msg 4108, Level 15, State 1, Line 3
Windowed functions can only appear in the SELECT or ORDER BY clauses.
(which is more clear).
|||Hello,
After some research, I tested out this syntax which works:
DELETE c
FROM (SELECT * FROM (SELECT RANK() OVER (PARTITION BY policy_details,CONVERT(CHAR(10),effectdate,103) ORDER BY historyid DESC) as num
FROM policy_details WHERE policy_status = '30240084' ) AS t
WHERE t.num>1) c|||
Hi Limno,
Thanks for your help with the SQL which works fine with my data.
I have written a small strored proc which deletes some data based on certain criteria which works fine but it takes approximately 30 minutes for one million records. Is there any way to write this stored proc logic into a single SQl query or sub queries?
The SP is :
-- ***********************************************************************
declare @.policy_details_id uniqueidentifier
declare @.Prepolicy_details_id uniqueidentifier
declare @.EffectDate datetime
declare @.CloseDate datetime
declare @.historyid int
DECLARE DATECur CURSoR FOR
select distinct(policy_details_id),effectdate,closedate,historyid from SM_Cust_policy_Details
where closedate = '2079-06-06 00:00:00.000' and derivative = 0
order by policy_details_id,effectdate
OPEN DATECur
FETCH NEXT FROM DATECur INTO @.policy_details_id, @.effectdate,@.CloseDate,@.historyid
WHILE @.@.FETCH_STATUS = 0
BEGIN
DELETE SM_Cust_policy_details where policy_details_id=@.policy_details_id
and historyid <> @.historyid
and closedate <> '2079-06-06 00:00:00.000'
and CONVERT(DATETIME,CONVERT(CHAR(10),effectdate,103)) >= CONVERT(DATETIME,CONVERT(CHAR(10),@.effectdate,103))
and policy_status_id = 30240084
and derivative = 0
FETCH NEXT FROM DATECur INTO @.policy_details_id, @.effectdate,@.CloseDate,@.historyid
END
CLOSE DATECur
DEALLOCATE DATECur
-- ***********************************************************************
Once again thanks for your help..
Cheers
Praveen
|||Can you explain what the query is trying to do in english rather than trying to decode your criteria. The potential query may differ depending the nature of the data in your table e.g. uniqueness and nullability of the columns.|||
Hi
I need to delete the table data based on the following criteria.
1) "policy_details_id" should be same
2) "effectdate" is greater than or equal to effectdate
3) "HistoryId" should not be same
4) "closedate" is not equal to ' 2079-06-06 00:00:00.000 '
5) "policy_status_id" should be 30240084
6) "Derivative" should be 0
Cheers
Praveen
|||
I can read your code. I got that much from the stored proc you posted. I'm trying to understand what the stored procedure is trying to achieve.
Should just one row per policy_details_id remain after the delete? Why are you doing distinct of the policy_details_id in you cursor query? why delete where effectdate is greater than @.effectdate? Wouldn't that delete more recent rows.
Like I said, I'm trying to understand what this query is doing "in english" and not "in SQL". An explanation similar to your original posting is what I'm after.
|||
Hi Adam,
Thanks for your reply and here are the answers for your queries.
******** Should just one row per policy_details_id remain after the delete?
Yes, I want to keep only one row per policy_details_id after the deletion.
****** Why are you doing distinct of the policy_details_id in you cursor query?
I used distinct caluse in order to reduce the cursor result set in stored proc. Anyway you can avoid distinct caluse in the sql query.
******** why delete where effectdate is greater than @.effectdate? Wouldn't that delete more recent rows
Yes, it is going to delete more recent rows.
Cheers
Praveen
|||
Hello:
Please check this query :
DELETE c
FROM (SELECT * FROM (SELECT *, RANK() OVER (PARTITION BY policy_details_id ORDER BY historyid DESC, effectdate) as num
FROM policy_details WHERE (closedate<> '2079-06-06 00:00:00.000' OR closedate is NULL) AND Derivative=0 AND policy_status_id = 30240084 ) AS t
WHERE t.num>1) c
|||
Hi Limno,
Needs to change the query.
First i need to do the following query..
1) select policy_details_id,effectdate,closedate,historyid from SM_Cust_policy_Details where closedate = '2079-06-06 00:00:00.000' and derivative = 0
Based on the result set of this query i need to do the following delete query.
2) DELETE SM_Cust_policy_details where policy_details_id = resultset_policy_details_id and historyid <> resultset_historyid and closedate <> '2079-06-06 00:00:00.000' and CONVERT(DATETIME,CONVERT(CHAR(10),effectdate,103))>=CONVERT(DATETIME,CONVERT(CHAR(10),resultset_effectdate,103)) and policy_status_id=30240084 and derivative = 0.
Note: resultset_policy_details_id,resultset_historyid,resultset_effectdate are the result set query values of policy_details_id,historyid,effectdate in query number 1.
How can i integrate both of the above queries and make a single query?
Cheers
Praveen
|||
Could you post a set of your sample data in your table and the expected result? Thanks.
|||
The data consists like this..
policy_details_id policy_status_id historyid effectdate closedate
70C36E97-9564-A048-0000-9665018B81FF 30240084 9 2004-09-11 12:00:00.000 2004-10-11 11:34:00.000
70C36E97-9564-A048-0000-9665018B81FF 30240084 10 2004-10-11 11:34:00.000 2005-09-11 11:47:00.000
70C36E97-9564-A048-0000-9665018B81FF 30240084 11 2005-09-11 12:00:00.000 2005-09-11 12:00:00.000
70C36E97-9564-A048-0000-9665018B81FF 14075352 12 2005-09-11 11:47:00.000 2079-06-06 00:00:00.000
First i need to consider the effectdate where closedate is '2079-06-06 00:00:00.000'.In this case it is '2005-09-11 11:47:00.000'.
I need to delete the records where effectdate(only datepart) is equal or greater than '2005-09-11' and policy_status_id should be 30240084.
In the above case the third record i.e historyid = 11 is going to be deleted.
Let me know if you have any problems to understand.
Cheers
Praveen
