Google Sheets Query - Sort By Date; Blanks/Null to the bottom

2
votes

Running into an issue when running a query in Google Sheets. The results of the array formula query are correct but the column utilized to order the results (Col1) is comprised of both blank/null cells and dates. As such, when ordered by this column the blank/null values are listed first before the dates. Is it possible to have the dates ranked first and push the blank/null cells to the bottom?

Ordering by DESC will not work as I would want the earlier dates listed first. Additionally, the blank/null cells cannot be excluded entirely from the results either (e.g. they correspond to tasks without deadlines but must still be listed).

The formula I am currently using is:

=ARRAYFORMULA((QUERY({DATA RANGE},"SELECT Col1 WHERE Col2 = X OR Col3 = X ORDER BY Col1 LIMIT 10",0))

Seems like there is an easy way to achieve this but I cannot find anything on the topic in other forums. Any help would be greatly appreciated.

1
datenullsql-order-bygoogle-sheets-query

1 Answers

0
votes

Use SORT()

I believe for your example you could make it work like so:

=SORT(ARRAYFORMULA((QUERY({DATA RANGE},"SELECT Col1 WHERE Col2 = X OR Col3 = X",0)), 1, 1) (untested)

If your LIMIT 10 is important, then I think you could wrap the whole thing in another query and re-add the LIMIT.

Illustrated Example

Range That Needs Querying and Sorting

enter image description here

Formula

Simple version defining a range in which the header is omitted:

=SORT(QUERY(A2:B7, "select *"), 1, 1)

Version that handles headers:

={A1:B1;SORT(QUERY(tabname!A2:B7, "select *"), 1, 1)}

This version creates an array combining the header row and the data rows so it can sort the data rows independently of the header.

Queried and Sorted Results

enter image description here

Breakdown of Formula Components

Array {[range 1]; [range 2]}

SORT() SORT([range], [column to sort on], [sort ascending - true/false or 1/0)

Query() QUERY([range], "[query]")