only show highest value user entry

Question

I am creating a contest where user can submit multiple entries. Only the entry with the highest tonnage will be shown. In the index view all the entries has to be sorted descending based on tonnage value.

My submissions controller shows following:

@submissions = @contest.submissions.maximum(:tonnage, group: User)

The problem here is that I do not get an array back with all the submission values. I need something I can iterate through.

e.g. a list which only contains one submissions from a user which is the submission with the highest tonnage value.

When I just group I get following error:

GroupingError: ERROR:  column "submissions.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "submissions".* FROM "submissions"  WHERE "submission...

UPDATE:

I found an sql query who does approximately what I want.

select * 
from submissions a
inner join
  ( select user_id, max(tonnage) as max_tonnage
    from submissions
    group by user_id) b 
on
a.user_id = b.user_id and
a.tonnage = b.max_tonnage

How can I fix this in activerecord?

Comment info:

score 0 · Accepted Answer · edited May 23 '17 at 12:06

0

Simpler with DISTINCT ON:

SELECT DISTINCT ON (user_id) * 
FROM   submissions
ORDER  BY user_id, tonnage DESC NULLS LAST;

NULLS LAST is only relevant if tonnage can be NULL:
Detailed explanation:

Select first row in each GROUP BY group?

Syntax in ActiveRecord:

Submission.select("DISTINCT ON (user_id) *").order("user_id, tonnage DESC NULLS LAST")

More in the Ruby documentation or this related answer:

Get a list of first record for each group

Possible performance optimization:

Optimize GROUP BY query to retrieve latest record per user

Sort result rows

Per request in comment.

SELECT * FROM (
   SELECT DISTINCT ON (user_id) * 
   FROM   submissions
   ORDER  BY user_id, tonnage DESC NULLS LAST
   ) sub
ORDER  BY tonnage DESC NULLS LAST, user_id;  -- 2nd item to break ties;

Alternatively use row_number() in a subquery:

SELECT * FROM (
   SELECT *
        , row_number() OVER (PARTITION BY user_id ORDER BY tonnage DESC NULLS LAST) AS rn
   FROM   submissions
   ) sub
WHERE  rn = 1
ORDER  BY tonnage DESC NULLS LAST, user_id;

Or the query you have, plus ORDER BY.

edited May 23 '17 at 12:06

Community

1
1

answered Aug 16 '15 at 12:03

Erwin Brandstetter

605,456
145
1,078
1,228

Thanks for the answer but I do not seem to get the highest tonnage per user id using your query. – Christoph Aug 16 '15 at 13:07
@Christoph: From what I see here, you *should*. In what way does it fail? Add your table definition to the question (what you get with `\d submissions` in psql) if the problem remains unsolved. – Erwin Brandstetter Aug 16 '15 at 13:12
I need to have submission per contest, I fixed it like this: @submissions = @contest.submissions.select("DISTINCT ON (user_id) *").order("user_id, tonnage DESC NULLS LAST") I do not want it to order on user id I want it to order on tonnage and descending. Now I see the users ordered on user id with their highest tonnage but I want those users ordered on tonnage desc. – Christoph Aug 16 '15 at 13:31
I made a print screen, look into the first post. The order should be chris1988, greg and then alec based on the tonnage values. and not sorting on user_id. – Christoph Aug 16 '15 at 13:36
Your question does not ask for any particular sort order for resulting rows. `ORDER BY` needs to agree with `DISTINCT ON`. *Follow the links for details.* To sort results differently, wrap this in a subquery or use a different query. I added some alternatives. – Erwin Brandstetter Aug 16 '15 at 13:48
thank, I am looking into it. I also modified my question so it is hopefully a bit more clear. – Christoph Aug 16 '15 at 13:53
Fixed it, thanks mate! – Christoph Aug 16 '15 at 14:52

only show highest value user entry

1 Answers1

Sort result rows