Get each ticket's latest status on MySQL 8 without DISTINCT ON

About this exercise

Support wants every ticket whose current status isn't 'closed'. The database is MySQL 8.0, so no DISTINCT ON. The current status is the newest event (ties on created_at go to the higher event id), a ticket with no events counts as 'new', and a ticket that was closed and then reopened must appear. Columns exactly ticket_id, title, current_status, ordered by ticket_id. One query in exactly 1 code block of at most 16 lines, under 30 words of text.

Common mistakes when you ask AI for this

• Not naming the database. The AI often writes Postgres-only DISTINCT ON, which fails on MySQL. Try: Add: "MySQL 8.0, so use ROW_NUMBER(), not DISTINCT ON."
• Filtering too early. Removing 'closed' rows before picking the latest makes a closed ticket look open again. Try: Add: "Pick the latest event first, then drop tickets whose latest is closed."
• Forgetting ties and empty tickets. Events in the same second need a tie-break, and tickets with no events vanish with an inner join. Try: Add: "Ties go to the higher id; no events means 'new' (LEFT JOIN + COALESCE)."
• Unnamed columns and extra versions. The AI picks its own column names and offers alternatives. Try: Add: "Columns exactly ticket_id, title, current_status. One query, one code block."

What a strong prompt includes

• Names the database
• Mentions the tie-break
• Handles tickets with no events
• Mentions reopened tickets
• Names the columns

Practice this now ↑

Related exercises

• Add a cron line that matches the others in a crontab (Coding AI · Product managers)
• Add dark mode by overriding your existing CSS variables (Coding AI · Software developers)
• Add input checks to your number guessing game (Coding AI · High school students)
• Add input validation to a Java method with exact messages (Coding AI · Software developers)
• Add labels to a signup form without rewriting the page (Coding AI · High school students)