Week 72 - SQL Chips and Chunks Newsletter

1. SQL Chip: Ranking Functions Demystified

MySQL ranking functions assign dynamic integer positions to records within specified dataset partitions based on sorting rules. ROW_NUMBER() outputs a strictly unique sequential integer for every row regardless of duplicate values. RANK() grants duplicate values the same position but skips subsequent ranks to reflect ties. DENSE_RANK() grants duplicate values the same position while continuing the integer sequence without leaving gaps.

2. SQL Chunk: Comparing Ranking Window Behaviors

The OVER() clause instructs MySQL to calculate window functions across designated column groupings while preserving raw rows. Comparing these three functions side-by-side reveals how each mechanism processes tied values during execution.

SELECT 
    patient_id,
    unit_code,
    vitals_score,
    ROW_NUMBER() OVER (PARTITION BY unit_code ORDER BY vitals_score DESC) AS row_num,
    RANK() OVER (PARTITION BY unit_code ORDER BY vitals_score DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY unit_code ORDER BY vitals_score DESC) AS dense_rnk
FROM patient_visits;

This single query outputs all three ranking variants simultaneously so you can evaluate how duplicate scores impact your dataset's index values.

3. SQL In Practice: Identifying Recent Patient Admissions

Healthcare operations regularly need to retrieve the single most recent check-in record per patient across clinical units. Partitioning records by patient_id and sorting check-in dates descending allows us to isolate the latest entry cleanly.

WITH latest_patient_checkins AS (
    SELECT 
        patient_id,
        unit_code,
        checkin_date,
        ROW_NUMBER() OVER (
            PARTITION BY patient_id 
            ORDER BY checkin_date DESC, visit_id DESC
        ) AS rn
    FROM patient_visits
)
SELECT 
    patient_id,
    unit_code,
    checkin_date
FROM latest_patient_checkins
WHERE rn = 1;

Filtering for rn = 1 inside the outer query guarantees that hospital admins receive exactly one current visit record per patient.

4. SQL Mistake: Filtering Window Functions in WHERE

A frequent error among SQL developers is attempting to filter window function outputs directly inside the WHERE clause. Because the WHERE clause evaluates before window calculation calculations complete, the database engine throws an illegal column alias error.

-- BROKEN QUERY
SELECT 
    patient_id,
    unit_code,
    vitals_score,
    ROW_NUMBER() OVER (PARTITION BY unit_code ORDER BY vitals_score DESC) AS rn
FROM patient_visits
WHERE rn <= 2;

-- CORRECTED QUERY
WITH ranked_visits AS (
    SELECT 
        patient_id,
        unit_code,
        vitals_score,
        ROW_NUMBER() OVER (PARTITION BY unit_code ORDER BY vitals_score DESC) AS rn
    FROM patient_visits
)
SELECT 
    patient_id,
    unit_code,
    vitals_score
FROM ranked_visits
WHERE rn <= 2;

Wrap your window calculation inside a CTE or derived table so the outer query can safely filter on the calculated rank alias.

5. SQL Challenge: Retaining Top Unit Scores Without Skipping Ranks

Clinical directors need a report listing the top two highest vital scores within each hospital department. If multiple check-ins tie for the top score, both records must be displayed without pushing lower scores out of the second-place ranking.

WITH unit_vitals_ranking AS (
    SELECT 
        patient_id,
        unit_code,
        vitals_score,
        DENSE_RANK() OVER (
            PARTITION BY unit_code 
            ORDER BY vitals_score DESC
        ) AS score_rank
    FROM patient_visits
)
SELECT 
    patient_id,
    unit_code,
    vitals_score,
    score_rank
FROM unit_vitals_ranking
WHERE score_rank <= 2
ORDER BY unit_code, score_rank ASC;

Using DENSE_RANK() guarantees that tied top scores both receive rank 1 while ensuring the next distinct score receives rank 2.

6. SQL Resource: MySQL Tutorial

MySQL Window Functions Guide

MySQL Tutorial is a clean, independent learning platform dedicated entirely to step-by-step database concepts. It breaks down complex analytical functions, window partitioning syntax, and performance optimization rules using clear query templates. This resource is invaluable to learners because it simplifies high-level database concepts into practical visual examples. Reviewing their window function guides directly helps you master complex DQL query building and analytical data processing in MySQL.