DISTINCT ON

Document: SELECT DISTINCT ON.

---

Good for getting unique rows from group of data. Contrived example of getting all unique student name and their test scores in a given exam:

sql = <<~SQL
  SELECT students.name, t.name, t.score
  FROM students
  INNER JOIN (
    SELECT DISTINCT ON (score) score, name
    FROM tests
    WHERE exam_id = :exam_id
    ORDER BY tests.score DESC
  ) t ON students.id = t.student_id
  ORDER BY t.score DESC
  LIMIT 10
SQL

exam = Exam.last
connection = ActiveRecord::Base.connection
student_name_scores_in_exam =
  connection.execute(sql, binds: { exam_id: exam.id }).values