1

I have a table having 3 columns (id, amount, time). The table is arranged in ascending order based on id. I want to remove all consecutive rows that have same value in the amount column except the first one.

eg: 

id: 12,15,16,17,19,22
amount: 555,546,546,53,53,500
time:

Answer:

id:12,15,17,22    amount: 555,546,53,500    time:

The time column is same for consecutive rows whenever the consecutive rows have same values in the amount column. I searched on SO but could not find a similar question

Aman
  • 353
  • 1
  • 3
  • 13
  • Refer this link : https://stackoverflow.com/questions/18439054/postgresql-with-delete-relation-does-not-exists , answer available in SO. – DebashisDeb Mar 02 '18 at 21:10
  • 1
    Duplicate of [How to delete duplicate entries](https://stackoverflow.com/questions/1746213/how-to-delete-duplicate-entries). Also, there's no such thing as "consecutive rows" in SQL, not like you're thinking. You can explicitly ask the database to `ORDER BY ` rows, but if you leave that clause out, the order you see is undefined. – Clockwork-Muse Mar 05 '18 at 05:34

2 Answers2

6

You can use lag():

select t.*
from (select t.*, lag(amount) over (order by id) as prev_amount
      from t
     ) t
where prev_amount is distinct from amount;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

This might not be an effective method but it works faster if you have large table (>2 million rows).

  1. Copy the table as csv format with DISTINCT on amount column:

    \COPY (SELECT DISTINCT ON (amount) * FROM Tablename) to '/home/user/table.csv' with csv delimiter ',';

  2. Truncate the previous table :

    TRUNCATE TABLE Tablename;

  3. Copy back the dumped table :

    \COPY Tablename from '/home/user/table.csv' with csv delimiter ',';

I've tried deleting duplicates but it took me a day for the query to complete. This method serves me well.

Boy
  • 26
  • 1