CODEWITHSUNDEEP
rubyruby-on-rails-3
rubyist
rubyist

Reputation: 3132

retrieve records by ascending order with multiple conditions

I need to get the largest of 3 amount fields from my table. But there are 2 amount fields in the Transaction table named expense_amt and income_amt. I need to get the largest of 3 amount fields by looking on both fields. I understand that I can query with multiple fields like

Model.order('expense_amt', 'income_amt').limit(3)

But this does not return largest of 3 amount fields as i expected. So basically i need to retrieve largest of 3 transactions. it would be like

Transactions
id    expense_amt   income_amt    transaction_type
1     100           NULL           1
2     200           NULL           3
3     NULL          400            1
4     NULL          800            2
5     1000          NULL           1

So the output of largest 3 would be [1000, 800, 400]

Upvotes: 0

Views: 67

Answers (2)

Arvind Kumar
Arvind Kumar

Reputation: 247

I have edit this solution 

    expense= Model.order('expense_amt DESC').limit(3).map{|exp| exp.expense_amt}
    income = Model.order('income_amt DESC').limit(3).map{|inc| inc.income_amt}
   result= expense +income
   result.sort{|a,b| b <=> a}.first(3)

Upvotes: 0

Magnuss
Magnuss

Reputation: 2310

This is not the prettiest solution, but maybe you can go somewhere from here :)

If the values cannot coincide, then the sum will always be the highest value. Using COALESCE SQL function, we replace NULL with 0 for the calculation to work.

Model.select("(COALESCE(expense_amt, 0) + COALESCE(income_amt, 0)) AS combined_amt").
  order("combined_amt DESC").
  limit(3).
  map{|row| row["combined_amt"]}

Upvotes: 1

Related Questions