Two queries both claim to produce quartiles, yet their boundaries disagree. PERCENTILE_CONT calculates percentile values, while NTILE assigns rows to buckets. Choose the operation matching your question before treating either result as a statistical cutoff.
Separate Value Boundaries From Row Assignment
The first quartile marks the twenty-fifth percentile of an ordered numeric population. The median marks the fiftieth percentile, and Q3 marks the seventy-fifth. A continuous percentile calculation can interpolate between adjacent observed values.
NTILE(4) answers a different question by distributing ordered rows among four numbered buckets. Its bucket numbers identify membership, not interpolated value thresholds. Calling both outputs quartiles without explaining that distinction invites inconsistent reports.
I ask whether the reader needs a threshold value or a balanced row assignment. I also specify how missing observations and duplicate values should be treated. Those choices affect the answer before any SQL performance question appears.
PERCENTILE_CONT is available in SQL Server 2012 and later. Its WITHIN GROUP syntax requires the supported database compatibility level, normally 110 or above. The examples use established functions rather than newer approximate percentile alternatives.
Test a Small Set With Duplicates and a High Value
Run the sample in one connection. It deliberately includes duplicate values and one comparatively high value. The separate observation identifiers provide stable ordering when assigning rows to buckets.
DROP TABLE IF EXISTS #Measurements;
CREATE TABLE #Measurements
(
ObservationId int NOT NULL PRIMARY KEY,
GroupId int NOT NULL,
Measurement decimal(12,2) NULL
);
INSERT #Measurements VALUES
(1,1,1),(2,1,2),(3,1,2),(4,1,3),
(5,1,4),(6,1,5),(7,1,9),(8,1,20),(9,1,NULL),
(10,2,10),(11,2,20),(12,2,30);
SELECT ObservationId, GroupId, Measurement
FROM #Measurements
ORDER BY GroupId, Measurement, ObservationId;The NULL represents missing information, not a measured zero. Both comparison methods below use the same non-NULL population. That avoids attributing a disagreement to method differences when the methods actually received different rows.
Do not remove duplicate observations merely because their values match. Two real measurements with the same value belong to the population twice. Deduplication changes the distribution unless the duplicates represent erroneous repeated ingestion.
The second group contains only three observations. That exposes the limits of describing very small populations as four complete buckets. It also makes the effect of percentile interpolation easier to inspect manually.
Calculate Quartiles as Continuous Percentile Values
SQL Server expresses PERCENTILE_CONT as a window calculation. The percentile result repeats for every row in the partition. DISTINCT below reduces those repeated values to one summary row per group.
SELECT DISTINCT GroupId,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Measurement)
OVER (PARTITION BY GroupId) AS Q1,
PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY Measurement)
OVER (PARTITION BY GroupId) AS MedianValue,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Measurement)
OVER (PARTITION BY GroupId) AS Q3
FROM #Measurements
WHERE Measurement IS NOT NULL
ORDER BY GroupId;For group 1's declared values, continuous interpolation implies Q1 of two and Q3 of six. Six does not appear among the sample observations. The median is three and a half, another legitimate interpolated result.
The query returned exactly those values on SQL Server 2025. The function returns float(53), so display rounding and exact-decimal requirements deserve attention. Casting for presentation does not change the underlying percentile convention.
PERCENTILE_DISC selects a value that exists in the distribution instead of interpolating. That is another valid method with a different contract. Name the chosen convention in the report so comparisons with other calculations remain meaningful.
Assign Rows to Quartiles With NTILE
NTILE balances row counts across the requested buckets. Where counts do not divide evenly, larger buckets come first and differ by at most one row. An input smaller than four cannot populate all four buckets.
;WITH Assigned AS
(
SELECT ObservationId, GroupId, Measurement,
NTILE(4) OVER
(PARTITION BY GroupId ORDER BY Measurement, ObservationId) AS BucketNumber
FROM #Measurements
WHERE Measurement IS NOT NULL
)
SELECT ObservationId, GroupId, Measurement, BucketNumber
FROM Assigned
ORDER BY GroupId, BucketNumber, Measurement, ObservationId;For the eight eligible observations in group 1, each bucket receives two rows. The equal values of two cross a bucket boundary. This is expected row balancing, not a promise to keep identical values together.
ObservationId provides deterministic assignment for tied values. Leaving ties unresolved can change which identical observation receives a particular bucket. It does not change the numeric values, but it matters when bucket membership drives another action.
If equal values must always remain together, use a threshold-based classification with an explicit equality rule. That can produce unequal bucket sizes. You cannot simultaneously guarantee equal row counts and indivisible ties for every possible population.
Calculate the Interquartile Range and Review Fences
The interquartile range is Q3 minus Q1. A common screening rule marks values below Q1 minus 1.5 IQR or above Q3 plus 1.5 IQR. These fences identify review candidates rather than automatically proving invalid data.
The following query applies each group's own continuous percentile values. It preserves the original observation identifier beside the screening result. That lets you investigate the source record rather than working backward from a rounded chart label.
;WITH Boundaries AS
(
SELECT ObservationId, GroupId, Measurement,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Measurement)
OVER (PARTITION BY GroupId) AS Q1,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Measurement)
OVER (PARTITION BY GroupId) AS Q3
FROM #Measurements
WHERE Measurement IS NOT NULL
), Fences AS
(
SELECT *, Q3 - Q1 AS InterquartileRange,
Q1 - 1.5 * (Q3 - Q1) AS LowerFence,
Q3 + 1.5 * (Q3 - Q1) AS UpperFence
FROM Boundaries
)
SELECT ObservationId, GroupId, Measurement, Q1, Q3,
InterquartileRange, LowerFence, UpperFence,
CASE WHEN Measurement < LowerFence OR Measurement > UpperFence
THEN 1 ELSE 0 END AS ReviewCandidate
FROM Fences
ORDER BY GroupId, Measurement, ObservationId;For the listed group 1 inputs, the upper fence is twelve. Its value of twenty therefore becomes a candidate under this rule. The query flags that row with ReviewCandidate set to one, which says nothing about production data.
Values exactly on a fence remain inside under the strict comparisons shown. Change that equality rule only when the reporting requirement specifies it. An IQR of zero also needs review. A repeated central value can make every different value fall outside the fences.
Never delete records solely because this expression returns one. A high value can be valid, and a small group can supply unstable thresholds. Add business context, source validation, and population size to the review process.
Keep Population and Method Visible in the Result
Partition by groups representing comparable measurements. Mixing different units or unrelated processes produces a mathematically calculable but unhelpful distribution. A shared data type does not establish a shared business meaning.
Report the eligible observation count and missing count alongside the boundaries. A group containing only NULL measurements supplies no eligible rows to these percentile queries. Use a separate group inventory when the report must display that group explicitly.
I test quartiles with duplicates, fewer than four observations, and a constant-valued population. I also compare the percentile values with the row assignments side by side. Four tidy buckets cannot make an untidy distribution agree with every interpretation.
Do you need balanced groups of records or numeric cutoffs for a distribution? Use that answer to choose the method. Preserve the convention and population rules so another reader can reproduce the same calculation.
Related reading on this blog: Approximate Percentiles With APPROX_PERCENTILE_CONT and What are T-SQL Median? Notes from the Field #090.
A quartile bucket is not a percentile boundary, it is an assignment of ordered observations.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
