2

Is there a way to specify the following in the order by statement in BigQuery, or do the equivalent?

SELECT * FROM books
ORDER BY books ASC (nulls first)

or:

SELECT * FROM books
ORDER BY books ASC (nulls last)

This would need to be within the item itself, not adding on a second item such as:

ORDER BY books IS NULL ASC, books ASC

Ideally, I'd like to apply it across an entire table (all queries for it) or something like that.

Here is this feature in Postgres: https://stackoverflow.com/a/9511492/651174

David542
  • 104,438
  • 178
  • 489
  • 842
  • as looks like you are rejecting in advance possible workarounds - looks like this is less of a question but rather a feature request and thus should go to respective tracker? – Mikhail Berlyant Mar 06 '18 at 23:32
  • @MikhailBerlyant right, I was seeing if this item is currently available in BQ or it's a feature not yet supported? – David542 Mar 06 '18 at 23:33
  • does'n look like supported in BigQuery – Mikhail Berlyant Mar 06 '18 at 23:34
  • 1
    but by default nulls are ordered as first for ASC and last for DESC. so if you need to revert this - how about something silly like (just as an simplified example) `order by IFNULL(book, 'zzzzzzzz')` for `nulls last` option for ASC and `nulls first` for DESC - just asking. Of course 'zzzzzz' should be domain specific – Mikhail Berlyant Mar 06 '18 at 23:37
  • [Here is the feature request](https://issuetracker.google.com/issues/35905901) for specifying the ordering of NULLs. – Elliott Brossard Mar 07 '18 at 00:37

1 Answers1

7

Since May 1st 2020, NULLS FIRST and NULLS LAST feature is available

https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#order_by_clause

Your example can be expressed as

SELECT * FROM books
ORDER BY books ASC NULLS FIRST

and

SELECT * FROM books
ORDER BY books ASC NULLS LAST
Yun Zhang
  • 5,185
  • 2
  • 10
  • 29