Intermediate9 min read PostgreSQL

DISTINCT ON & LATERAL — Latest Row per Group, Top-N per Group

Two PostgreSQL tools for "one row per X" questions: DISTINCT ON for the latest record per key, and LATERAL joins for top-N per group and reusable computed columns.

DISTINCT ON
LATERAL
top-N per group
latest record
ROW_NUMBER

Use this when you need to:

Show each application's current status from a history table that keeps every change
Find every student's most recent term and what their status was in it
List the three highest-enrolled sections in each term for the scheduling committee
Compute quality points once and reuse them in two more columns without repeating the formula

Both of these are PostgreSQL extensions to standard SQL, and both replace patterns that are longer and slower in portable SQL. DISTINCT ON keeps the first row for each value of the columns you name, where "first" is decided by ORDER BY. LATERAL lets a subquery in FROM look at the row to its left, which is what makes "top three per group" a two-line subquery instead of a window-function CTE.

DISTINCT ON — the latest status per application

ods_appl_status_history keeps every status an application has ever had. To get the current one you need the newest row per application. DISTINCT ON (applications_id) keeps exactly one row per application; the ORDER BY decides which — so sort by the key first and then by "newest first".

SELECT DISTINCT ON (h.applications_id)
  h.applications_id,
  h.appl_status,
  h.application_status_desc,
  h.appl_status_date
FROM ods_appl_status_history h
ORDER BY h.applications_id, h.appl_status_date DESC, h.pos DESC;

The ORDER BY must begin with the DISTINCT ON columns, in the same order — PostgreSQL raises an error otherwise. Everything after them is the tie-breaker that picks the winner. Here pos DESC settles two status changes on the same day.

The portable alternative — ROW_NUMBER

-- Same result. Longer, but it also lets you keep the top 2 or 3 by changing rn = 1.
WITH ranked AS (
  SELECT
    h.applications_id,
    h.application_status_desc,
    h.appl_status_date,
    ROW_NUMBER() OVER (
      PARTITION BY h.applications_id
      ORDER BY h.appl_status_date DESC, h.pos DESC
    ) AS rn
  FROM ods_appl_status_history h
)
SELECT applications_id, application_status_desc, appl_status_date
FROM ranked
WHERE rn = 1
ORDER BY applications_id;

Latest term per student

-- "Most recent" means latest term_start_date, never the largest term code
SELECT DISTINCT ON (st.sttr_student)
  st.sttr_student,
  t.terms_id       AS latest_term,
  st.status_desc,
  st.standing_desc
FROM ods_student_terms st
  JOIN ods_terms t ON t.terms_id = st.sttr_term
ORDER BY st.sttr_student, t.term_start_date DESC;

LATERAL — top-N per group

A normal subquery in FROM cannot reference the tables beside it. A LATERAL subquery can, and it is evaluated once per row of the table on its left. Combine that with ORDER BY … LIMIT 3 inside the subquery and you have the three largest sections in each term, with the term's columns still available outside.

SELECT
  t.terms_id,
  top.sec_name,
  top.enrolled
FROM ods_terms t
  JOIN LATERAL (
    SELECT cs.sec_name, cs.active_student_count::int AS enrolled
    FROM ods_course_sections cs
    WHERE cs.sec_term = t.terms_id            -- refers to the outer row
    ORDER BY cs.active_student_count::int DESC, cs.sec_name
    LIMIT 3
  ) top ON true
ORDER BY t.term_start_date, top.enrolled DESC;

active_student_count is stored as text (varchar) in this schema, so it is cast to int before sorting — otherwise '9' sorts after '25'. The ON true is required syntax for JOIN LATERAL; the real join condition lives inside the subquery's WHERE.

LEFT JOIN LATERAL keeps the groups with nothing to show

-- Terms with no sections still appear, with NULLs — useful for spotting empty periods
SELECT
  t.terms_id,
  top.sec_name,
  top.enrolled
FROM ods_terms t
  LEFT JOIN LATERAL (
    SELECT cs.sec_name, cs.active_student_count::int AS enrolled
    FROM ods_course_sections cs
    WHERE cs.sec_term = t.terms_id
    ORDER BY cs.active_student_count::int DESC, cs.sec_name
    LIMIT 1
  ) top ON true
ORDER BY t.term_start_date;

LATERAL as a named calculation

You cannot reference a SELECT-list alias elsewhere in the same SELECT list. A one-row CROSS JOIN LATERAL gives the calculation a name you can reuse, which keeps a formula in one place.

SELECT
  scs.scs_student,
  scs.scs_course_name,
  scs.scs_credits,
  scs.scs_grade_pts,
  qp.quality_points,
  ROUND(qp.quality_points / NULLIF(scs.scs_credits, 0), 2) AS check_gpa
FROM ods_stu_course_sec scs
  CROSS JOIN LATERAL (
    SELECT scs.scs_credits * scs.scs_grade_pts AS quality_points
  ) qp
WHERE scs.scs_term = '2026SP'
  AND scs.scs_verified_grade IS NOT NULL
ORDER BY scs.scs_student, scs.scs_course_name
LIMIT 10;

Practice challenge

For every student, return their most recently graded course: the term, the course name and the verified grade. "Most recent" must be decided by the term's start date. Break ties within a term alphabetically by course name.

Solution

SELECT DISTINCT ON (scs.scs_student)
  scs.scs_student,
  t.terms_id             AS term,
  scs.scs_course_name    AS course,
  scs.scs_verified_grade AS grade
FROM ods_stu_course_sec scs
  JOIN ods_terms t ON t.terms_id = scs.scs_term
WHERE scs.scs_verified_grade IS NOT NULL
ORDER BY scs.scs_student, t.term_start_date DESC, scs.scs_course_name;

Practise this on real university data

Write real PostgreSQL in your browser and get it graded on the rows it returns. No signup and nothing to install for your first query.

Run your first query Browse practice problems

See all 21 lessons