RE: Odd (to me) Behavior with NTILE() and NULL Values in Source Data

SSC Guru

Points: 61136

February 22, 2016 at 10:28 am

EdgeOfKnowWare (2/22/2016)
Maybe a work around would be something like this:
SELECT *,
CASE WHEN val IS NULL THEN 0 ELSE 1 END,
NTILE(4) OVER (PARTITION BY CASE WHEN val IS NULL THEN 0 ELSE 1 END ORDER BY val) As Q
FROM #t;
Only use the rank when CASE WHEN val IS NULL THEN 0 ELSE 1 END = 1

I suspect that you were trying to do something like this:

SELECT *,

CASE WHEN val IS NULL THEN 1 ELSE NTILE(4) OVER (ORDER BY val) END-1 As Q

FROM #t;

... which will assign a 0 to NULL values and apply NTILE(4) to the non-null values.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001