Delete next row if values are less than previous row

Question

Example,

    SELECT * FROM stats_table
order by accuracy desc, totals desc;

This is ordered by accuracy desc, totals desc

and it returns the following

id  col_a       amount  col_b       accuracy   totals
881 stat_a      38.0    stat_b      71.60       162 
884 stat_a      39.0    stat_b      70.52       173 
109 stat_a      38.0    stat_b      69.91       105 
880 stat_a      38.0    stat_b      69.88       249 
883 stat_a      39.0    stat_b      69.47       262

I want the row removed if the accuracy AND totals is both lower than the row above, so from the example, id 109 should be removed as accuracy and totals are both lower than the row above, id 884

So it should end up like this

id  col_a       amount  col_b       accuracy   totals
881 stat_a      38.0    stat_b      71.60       162 
884 stat_a      39.0    stat_b      70.52       173 
880 stat_a      38.0    stat_b      69.88       249 
883 stat_a      39.0    stat_b      69.47       262

Ideally, as there are 1000s of rows, it should loop through the whole table.

please help

You're sure about that? That means it will only reserve the first row. — Blank, Jul 01 '16 at 10:12
row id 109 should be deleted as both accuracy and totals a less than id 884 — Michael Cole, Jul 01 '16 at 10:13
Please, edit your question instead. Data in comments are so hard to read. — jarlh, Jul 01 '16 at 10:13
@10086 only the one row should be removed as it is the only row where the accuracy and totals are both lower than the row above. — Michael Cole, Jul 01 '16 at 10:18
You say the row above, but how do we know the order? It's obviously not id order... — jarlh, Jul 01 '16 at 10:19
no its not, as mentioned, its ordered by accuracy desc, totals desc — Michael Cole, Jul 01 '16 at 10:20
Set row number then Prepare a new table while you compare totals in a loop. New table will be your wanted result. — neer, Jul 01 '16 at 10:49

score 0 · Answer 1 · answered Jul 01 '16 at 10:34

0

To do it in the way you have described you could assign a row number:

select *
from stats_table
where id in (
  select a.id
  from (SELECT *, row_number()over(order by accuracy desc, totals desc) as row FROM stats_table) a
  inner join (SELECT *, row_number()over(order by accuracy desc, totals desc) as row FROM stats_table) b
  on a.row = b.row + 1
  and a.accuracy < b.accuracy
  and a.total < b.total
)

Then you just need to replace 'select *' with 'delete from' when happy that the rows you want deleted have been selected (I'm unable to test the code for obvious reasons).

answered Jul 01 '16 at 10:34

Vorpulus Lyphane

660
6
19

thank you for the reply, im getting an error 1064, in the SQL syntax. – Michael Cole Jul 01 '16 at 10:37
@Vorpulus: mysql doesn't have window functions, I'm afraid. – joop Jul 01 '16 at 10:41
Oh right. Only other thing I can think of is using an [identity column](http://stackoverflow.com/questions/14753321/add-auto-increment-id-to-existing-table). Good luck. – Vorpulus Lyphane Jul 01 '16 at 11:00

Delete next row if values are less than previous row

1 Answers1