Ignoring null values in in a postgresql rank() window function
postgresql, sql
Solution
Just use a `case` statement around the `rank()`:
select Person,
(case when arrival_time is not null
then rank() over (partition by Restaurant order by arrival_time asc)
end) as arrival_rank
from dinner_show_up;
Problem
I am writing a SQL query using PostgreSQL that needs to rank people that "arrive" at some location. Not everyone arrives however. I am using a `rank()` window function to generate arrival ranks, but in the places where the arrival time is null, rather than returning a null rank, the `rank()` aggregate function just treats them as if they arrived after everyone else. What I want to happen is that these no-shows get a rank of `NULL` instead of this imputed rank. Here is an example. Suppose I have a table `dinner_show_up` that looks like this: ``` | Person | arrival_time | Restaurant | +--------+--------------+------------+ | Dave | 7 | in_and_out | | Mike | 2 | in_and_out | | Bob | NULL | in_and_out | ``` Bob never shows up. The query I'm writing would be: ``` select Person, rank() over (partition by Restaurant order by arrival_time asc) as arrival_rank from dinner_show_up; ``` And the result will be ``` | Person | arrival_rank | +--------+--------------+ | Dave | 2 | | Mike | 1 | | Bob | 3 | ``` What I want to happen instead is this: ``` | Person | arrival_rank | +--------+--------------+ | Dave | 2 | | Mike | 1 | | Bob | NULL | ```