Help with SQL

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