deleteion - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

deleteion

Author

Topic

amurark
Yak Posting Veteran

55 Posts

Posted - 2011-08-02 : 04:20:00

please tell me how to delete duplicate data.Only the latest time data shuld be there. for eg

7/28/2011 22:52 00215C9F5D03

7/27/2011 6:56 00215C9F5D03

5/13/2011 9:42 00215C9F5D03

i want only first record else two i want to delete

output should be like this
7/28/2011 22:52 00215C9F5D03

please repy asap

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-02 : 04:32:52

Why the urgency ?


DELETE D
FROM   (
           SELECT *, row_no = row_number() over(partition by some_col order by date_col desc)
           FROM   yourtable
       ) D
WHERE  D.row_no <> 1

KH
[spoiler]Time is always against us[/spoiler]

amurark
Yak Posting Veteran

55 Posts

Posted - 2011-08-02 : 13:32:47

coz i wrk

thnks for ur repy
wht is D here

regards
ankita

Ankita

amurark
Yak Posting Veteran

55 Posts

Posted - 2011-08-02 : 13:32:53

coz i wrk

thnks for ur repy
wht is D here

regards
ankita

Ankita

amurark
Yak Posting Veteran

55 Posts

Posted - 2011-08-02 : 23:07:34

it worked
but i am not able to understand y we used rowno there

culd u plese expalin this

regards
ankita

Ankita

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-02 : 23:20:13

[code]
DELETE D
FROM (
SELECT *, row_no = row_number() over(partition by some_col order by date_col desc)
FROM yourtable
) D
WHERE D.row_no <> 1
[/code]

D is the alias given to the derived table

row_number() will numbered the rows starting from 1 with increment of 1 in the order / sequence specify by the ORDER BY

The sequential number will restart from 1 for every different value in somecol (PARTITION BY)

Run the query below and you will see the effect of row_no
[code]
SELECT *, row_no = row_number() over(partition by some_col order by date_col desc)
FROM yourtable
[/code]

You will see that for every "some_col", row_no will be 1 for the latest date and row_no = 2 for next latest and so on.

KH
[spoiler]Time is always against us[/spoiler]

amurark
Yak Posting Veteran

55 Posts

Posted - 2011-08-03 : 03:04:43

yes thanks alot

regards
ankita murarka

Ankita

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources