Sorting nulls last

Hi. Is there a way to sort a column in a table in descending order with nulls last instead of first? The default behavior (nulls appear at the top) is most painful in merged results where a coalesce on LookML level is not possible. I can imagine that in a vast majority of cases nulls actually mean 0 and this seems like such a basic functionality that I wonder if I am not missing some obvious solution…

Solved Solved
1 17 2,940
1 ACCEPTED SOLUTION

Hi @Prasham_Ashesh

I actually haven’t gotten to try it yet… but my first stab at it would be something like:

measure: foo {
  ...
  order_by_field: foo_nulls_last
}

measure: foo_nulls_last {
 type: <depends on type of foo, either number or string>
 sql: CASE WHEN ${foo} is NULL THEN <value that sorts last> ELSE ${foo} END ;;
}

View solution in original post

17 REPLIES 17

I am looking for the same solution…

chy353
Participant I

I’ve always just added a table calculation that refers to the column you are referencing to and just do an if statement if that is null then result in 0. That way I can sort that new table calc and hide it, which will sort your results and can let the nulls be last.

if(is_null(${measure}),0,${measure})

@chy353 true, but this doesn’t work if you only display limited results (like top 500) because then you cannot sort by a calculation. Also, this is very annoying if you have many metrics in a report and have to create a set of calculations just for sorting purposes.
To my mind there should be an setting on metric / dimension level in LookML that lets set sort nulls first / last.

Hi Magda 🙂

There is a enhancement that we’re working on right now which should help as a workaround - allowing you to declare order_by_field on measures! Once it is released in the near future, you’ll be able to use it to affect null sort order.

Of course, it is still a workaround, and the ability to more explicitly control null sort order is still on our radar

order_by_field on measures will be in the 7.4 realease coming this month!

How would we do Order By NULL LAST using order_by_fields? I can’t use Table Calculations, as in the Table I have more than 5000 rows
@fabio1

Hi @Prasham_Ashesh

I actually haven’t gotten to try it yet… but my first stab at it would be something like:

measure: foo {
  ...
  order_by_field: foo_nulls_last
}

measure: foo_nulls_last {
 type: <depends on type of foo, either number or string>
 sql: CASE WHEN ${foo} is NULL THEN <value that sorts last> ELSE ${foo} END ;;
}

Thanks a lot @fabio1 I implemented the solution as suggested, but I had to give a large Constant value in <value that sorts last>, but I wanted to avoid that.

This Solution would work perfectly in normal cases but, I am calculating Days since Last Activity. If No Activity is Found I receive NULL otherwise, the corresponding difference between Current Date and Last Date of Activity , is Calculated.
I want that If I sort Days since Last Activity in Decreasing Order, NULLs should Appear first then others in Decreasing order and vice versa for Ascending Order, which I was able to achieve.

Though, In order to place NULLs last, for the above solution, I provided a large constant value in place of <value that sorts last> which was something I was trying to avoid. As at some point in Time, Days since Last Activity would most likely overtake the Constant value that I define here. It is okay for now because it would take years for that to happen, but still is there a way to avoid, giving a Large constant?

Thanks a lot for your help @fabio1

Hi @Prasham_Ashesh - I don’t think so. We know it’s just a partial workaround, which is why we still have left open the broader request to be able to explicitly control null sort order

Hey! What are the constraints of implementing looking at null as the smallest value? Seems to be a very straight forward fix. Don’t think that this issue was reported just 11 months ago. I am using Looker for over 3 years now and it was always an issue.

I was really wondering about the same thing. We’ve been scraping Instagram followers for quite some time now and given that many of the attributes gathered are null because of the nature of the different profiles (private, public, with email, without, phone number etc) wanted to sort null as last.

So thanks for the questions, much appreciated. 

Hey, I have come across this problem. I want to keep the nulls in the data but them to be sorted last. Is this possible without converting the nulls to a different value?

Dmitri_S
Participant I

Hello @fabio1 - are you aware where Looker about control null sort order?

In our case we have to control it for 50+ measures, so I’m currently looking for some dynamic liquid...

Hello @fabio1 - are you aware where Looker about control null sort order?

In our case we have to control it for 50+ measures, so I’m currently looking for some dynamic liquid...

I don’t know of any way to cause the order_by field to dynamically reflect the field that uses it. The best I can think of is setting up a linter rule to check to make sure the explicitly defined order_by fields are correctly paired.

Dmitri_S
Participant I

@fabio1 thank you for the response and suggestion about Linter! And it seems there is no direct way to handle it by Looker yet. 

Considering that we need it for API requests and it’s possible to know which field is sorted the solution can be also using parameter + dynamic measure:

view: fov {

parameter: field_to_sort {
type: unquoted
hidden: yes
allowed_value: {value: "table_1.msr_1"}
allowed_value: {value: "table_1.msr_2"}
...
allowed_value: {value: "table_1.msr_K"}
allowed_value: {value: "table_2.msr_1"}
allowed_value: {value: "table_2.msr_2"}
...
allowed_value: {value: "table_L.msr_N"}
}

measure: nulls_last_dynamic_msr{
type: number
hidden: yes
sql: IFNULL("{% parameter field_to_sort %}",-1) ;;
}
}

Considering that we need it for API requests and it’s possible to know which field is sorted the solution can be also using parameter + dynamic measure:

Ah, yes, if your use case is from the API and therefore it’s not too much burden to specify something twice in the request, then you can definitely make a single measure that could sort differently depending on the parameter value. What you’ve proposed above looks like a good idea

We can use sorting the values in lookml. You can refer the below syntax.

Add

 order_field_by : sortable ( at the existing dimension)

dimension: sortable {

    type: number

    sql: case

          when TRIM(${grade_level}) = 'K' then 0

          when TRIM(${grade_level})= '1' then 1

          when TRIM(${grade_level}) = '2' then 2

          when TRIM(${grade_level}) = '3' then 3

          when TRIM(${grade_level}) = '4' then 4

          when TRIM(${grade_level}) = '5' then 5

          when TRIM(${grade_level}) = '6' then 6

          when TRIM(${grade_level}) = '7' then 7

          when TRIM(${grade_level}) = '8' then 8

          when TRIM(${grade_level}) = '9' then 9

          when TRIM(${grade_level}) = '10' then 10

          when TRIM(${grade_level}) = '11' then 11

      else 12

        end  ;;

  }

Top Labels in this Space
Top Solution Authors