delete nth row from table (postgresql)

Question

I know its possible to select nth row.

For example

select from table limit 1 offset 3;

Is it possible to delete nth row?

Tables represent unordered sets. There is no such thing as the nth row in a table. — Gordon Linoff
– Gordon Linoff, Commented Mar 30, 2017 at 12:59

SqlZim · Accepted Answer · 2017-03-30 13:07:55Z

1

If you have an id and something to order by and/or partition by you can delete using row_number() like so:

drop table if exists t;
create table t (id int, val int);
insert into t values (1,9),(2,8),(3,7),(4,6),(5,5);

delete 
from t
where id in (
  select id
  from (
    select id, row_number() over (order by val asc) as rn
    from t
    ) s
  where s.rn = 3);

select * from t;

rextester demo: http://rextester.com/XJHB50704

returns:

+----+-----+
| id | val |
+----+-----+
|  1 |   9 |
|  2 |   8 |
|  4 |   6 |
|  5 |   5 |
+----+-----+

answered Mar 30, 2017 at 13:07

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

delete nth row from table (postgresql)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related