CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3postgresqlactiverecord
whitesiroi
whitesiroi

Reputation: 2833

How to delete data from DB with the same dates except the biggest date. Rails 3

I have date like:

=> #<Item id: 8749, asin: "B000V2ACH8", created_at: "2014-03-24 00:15:24">
=> #<Item id: 8750, asin: "B000V2ACH8", created_at: "2014-03-24 14:35:24">
=> #<Item id: 8751, asin: "B000V2ACH8", created_at: "2014-03-24 19:35:24">
=> #<Item id: 8752, asin: "B000V2ACH8", created_at: "2014-03-24 17:35:24">
...
=> #<Item id: 8753, asin: "7770V2A888", created_at: "2014-03-24 21:58:24">
=> #<Item id: 8754, asin: "7770V2A888", created_at: "2014-03-24 05:24:24">
...
=> #<Item id: 8755, asin: "7770V2A888", created_at: "2014-03-23 23:58:24">
=> #<Item id: 8756, asin: "7770V2A888", created_at: "2014-03-23 22:58:24">
...
=> #<Item id: 8757, asin: "7770V2A888", created_at: "2014-03-22 19:58:24">
=> #<Item id: 8759, asin: "7770V2A888", created_at: "2014-03-22 10:58:24">
=> #<Item id: 8760, asin: "7770V2A888", created_at: "2014-03-22 23:33:33">

How to get max(create_at) of the same year-month-date & delete rest with the same asin & the same year-month-date(lower than max of that date)? Want to get values like ⬇

=> #<Item id: 8751, asin: "B000V2ACH8", created_at: "2014-03-24 19:35:24">
=> #<Item id: 8753, asin: "7770V2A888", created_at: "2014-03-24 21:58:24">
=> #<Item id: 8755, asin: "7770V2A888", created_at: "2014-03-23 23:58:24">
=> #<Item id: 8760, asin: "7770V2A888", created_at: "2014-03-22 23:33:33">

Upvotes: 1

Views: 204

Answers (3)

whitesiroi
whitesiroi

Reputation: 2833

Definitely not the best answer, but it works for me :) Example :)

  @item.try(:each) do |i|
    for j in 0..10
      group_records = Item.where(:asin => i.asin, :domain => i.domain, :user_id => i.user_id).where(:created_at => Date.today.beginning_of_day - j.day..Date.today.end_of_day - j.day)
      max_date = group_records.select('max(created_at) as m').first.m
      group_records.where('created_at != ?', max_date).delete_all
    end
  end

Upvotes: 0

Raj
Raj

Reputation: 22926

Using SQL:

DELETE items
FROM items
INNER JOIN (select asin,cast(created_at) created_date, max(created_at) as m
from items group by asin) max_items ON max_items.asin = asin.asin
AND max_items.created_date = cast(items.created_at as DATE)
WHERE items.created_at < max_items.created_at

Upvotes: 1

alex
alex

Reputation: 3742

group_records = Item.where('id>0') # you can put any conditions inwhere` clause

max_date = group_records.select('max(created_at) as m').first.m
group_records.where('created_at < ?', max_date).delete_all

Upvotes: 1

Related Questions