CODEWITHSUNDEEP
sqlsql-serverwindow-functionssql-deletesql-server-2017
Alpha
Alpha

Reputation: 45

Random Rows Delete

I need to delete one row and keep the next two depending on order by ColumnName

╔═════╦═════════════╦═══════╦════════════╦════════╗
║ ID  ║ Description ║ Value ║    Date    ║ Number ║
╠═════╬═════════════╬═══════╬════════════╬════════╣
║ 0x0 ║ Desc        ║   100 ║ 2020/01/01 ║    200 ║
║ 0x0 ║ Desc1       ║   150 ║ 2020/01/01 ║    201 ║
║ 0x0 ║ Desc2       ║    75 ║ 2020/01/01 ║    202 ║
║ 0x0 ║ Desc3       ║    50 ║ 2020/01/01 ║    500 ║
║ 0x0 ║ Desc4       ║    55 ║ 2020/01/01 ║    505 ║
║ 0x0 ║ Desc5       ║   110 ║ 2020/01/01 ║    507 ║
╚═════╩═════════════╩═══════╩════════════╩════════╝

For example rows number 202 and 507 should deleted.
Is it possible?

Upvotes: 0

Views: 541

Answers (2)

GMB
GMB

Reputation: 222582

If you want to delete one row out of three in the whole table, you can use row_number() and a, updatable CTE:

with cte as (
    select row_number() over(order by number) rn
    from mytable
)
delete from cte where rn % 3 = 0

row_number() enumerates rows starting at 1 for the smallest number; then, the outer query deletes every third row. For your sample data, this would delete rows with numbers 202 and 507.

Upvotes: 1

shawnt00
shawnt00

Reputation: 17925

Yes, you can number the rows and then delete them. Be very careful with this. I certainly don't recommend using this technique very often.

with data as (
    select *, row_number() over (order by Number) as rn from T
)
delete from data
where rn % 3 = 0;

Be aware that row_number() will start with a value of 1 rather than 0.

Upvotes: 1

Related Questions