Order by conditional count of associated records

Question

One quiz can have none, one or many submissions. Each submission has submissions.correct = t or submissions.correct = f.

What's the best Postgres 9.4 query to get all quizzes (regardless of whether they have a Submission or not), ordered by the number of associated submissions with correct = t in ASC order so that the quizzes that have the least amount of associated submissions.correct = t come first?

db=# \d quizzes;
                                         Table "public.quizzes"
       Column   |            Type             |                      Modifiers                       
    ------------+-----------------------------+------------------------------------------------------
     id         | integer                     | not null default nextval('quizzes_id_seq'::regclass)
     question   | character varying           | not null
     created_at | timestamp without time zone | not null
     updated_at | timestamp without time zone | not null
    Indexes:
        "quizzes_pkey" PRIMARY KEY, btree (id)
    Referenced by:
        TABLE "submissions" CONSTRAINT "fk_rails_04e433a811" FOREIGN KEY (quiz_id) REFERENCES quizzes(id)
        TABLE "answers" CONSTRAINT "fk_rails_431b8a33a3" FOREIGN KEY (quiz_id) REFERENCES quizzes(id)

db=# \d submissions;
                                         Table "public.submissions"
       Column   |            Type             |                        Modifiers                         
    ------------+-----------------------------+----------------------------------------------------------
     id         | integer                     | not null default nextval('submissions_id_seq'::regclass)
     quiz_id    | integer                     | not null
     correct    | boolean                     | not null
     created_at | timestamp without time zone | not null
     updated_at | timestamp without time zone | not null
    Indexes:
        "submissions_pkey" PRIMARY KEY, btree (id)
        "index_submissions_on_quiz_id" btree (quiz_id)
    Foreign-key constraints:
        "fk_rails_04e433a811" FOREIGN KEY (quiz_id) REFERENCES quizzes(id)

Very clear description and proper table definitions. If you also included your version of Postgres and what you tried so far (even if it's not working), this would be an exemplary question. — Erwin Brandstetter, Feb 13 '15 at 04:27

score 1 · Accepted Answer · edited May 23 '17 at 12:20

SELECT q.*, s.ct
FROM   quizzes q
LEFT   JOIN (
   SELECT quiz_id, count(*) AS ct
   FROM   submissions
   WHERE  correct
   GROUP  BY 1
   ) s ON s.quiz_id = q.id
ORDER BY s.ct NULLS FIRST;

Since you want all quizzes, it's probably fastest to aggregate first and join later.
Make it a LEFT JOIN to keep quizzes without submissions in the result.
NULLS FIRST is crucial here, so that quizzes without any (correct) submissions come first.
- PostgreSQL sort by datetime asc, null first?
Unlike some other popular RDBMS, Postgres has a proper boolean type. The expression correct = t is exactly the same as just correct.

Order by conditional count of associated records

1 Answers1