Delete rows from resultset in SQL server

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

SQL Server 2005 Forums

Transact-SQL (2005)

Delete rows from resultset in SQL server

Author

Topic

clement.store
Starting Member

25 Posts

Posted - 2009-05-11 : 05:02:42

Dear all,

I have a table YYData with the Columns: DealerCode, period, PeriodYear, Sales, Acct.
I have used the following script to find out the some rows.

"select distinct DealerCode, period, PeriodYear from YYData"

DealerCode period PeriodYear
12 1 2009
12 4 2009
12 2 2009
12 0 2009
12 3 2009
14 1 2009
14 0 2009

Now I want to delete all rows in YYData that meets rows criteria found by the Distinct statement.

Would u pls show me an efficient code to accomplish it.

Thanks and regards,
Clement

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-11 : 05:04:54

[code]
delete d
from YYData d
inner join
(
select distinct DealerCode, period, PeriodYear
from YYData
) r on d.DealerCode = r.DealerCode
and d.period = r.period
and d.PeriodYear = r.PeriodYear
[/code]

Delete all or keep 1 row ?

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

clement.store
Starting Member

25 Posts

Posted - 2009-05-11 : 05:09:01

I see your code deletes all. THanks.
HOw to Keep one row pls.. ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-11 : 05:13:44

which row do you want to keep ?

The query below will keep rows with row_no = 1.
Try changing the ORDER BY in the over() and try to run the query by itself to see the result.


delete d
from   (
           select DealerCode, period, PeriodYear,
                  row_no = row_number() over (partition by DealerCode, period, PeriodYear order by Sales)
           from   YYData
       ) d
where  d.row_no > 1

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

clement.store
Starting Member

25 Posts

Posted - 2009-05-11 : 05:18:19

Thanks! U have been very helpful.. tell u what i just went to Orchard road 2 months ago and found the pepper crab so great...! Also rided the ferris wheel.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-11 : 05:21:20

You are welcome. Glad you like the crab