CODEWITHSUNDEEP
ruby-on-railsmongodb
Andrew Hall
Andrew Hall

Reputation: 3073

Getting highest value across 2 tables

I am trying to get the highest score for a particular user from 2 different tables.

Example:

Table 1
{user=>"Dave", score=>8}
{user=>"Dave", score=>2}
{user=>"Frank", score=>5}

Table 2
{user=>"Bill", score=>5}
{user=>"Dave", score=>3}
{user=>"Frank", score=>7}

The result that I want to get is the highest score for each user, ordered by score descending.

Dave: 8 Frank: 7 Bill: 5

I think perhaps I need to use map/reduce but I am not too sure I tried to use a Hash to iterate through results, but as I was using the name as the key, values were being overwritten.

Thanks Andrew

Upvotes: 0

Views: 48

Answers (2)

usha
usha

Reputation: 29369

you can do this in a sql query by joining the two tables on name.

SELECT
    CASE
        WHEN Table1.score >= Table2.score THEN Table1.score
        ELSE Table2.score
    END AS top_score, name
FROM Table1 join Table2 on Table1.name=Table2.name.

Upvotes: -1

Sergey Kuznetsov
Sergey Kuznetsov

Reputation: 8721

Here is an example of how to achieve your sorting on Ruby side:

> unsorted = [{:user=>"Bill", :score=>5}, {:user=>"Dave", :score=>3}, {:user=>"Frank", :score=>7}]
 => [{:user=>"Bill", :score=>5}, {:user=>"Dave", :score=>3}, {:user=>"Frank", :score=>7}]
> unsorted.sort_by { |r| r[:score] }.reverse
 => [{:user=>"Frank", :score=>7}, {:user=>"Bill", :score=>5}, {:user=>"Dave", :score=>3}]

Upvotes: 2

Related Questions