Sorting behavior specification in order by for window function in GBQ

I am running a query in GBQ using window function. but I don’t exactly know how to specify sorting behavior. In order by column I have two columns which I want to sort rows by them in descending order. for that I used DESC at the end of order by clause but what I got is that it orders the rows first on ascending order for the first column and then descending on the second. my question is if I should specify sorting order for each column ?

here is the query which returns wrong order:

SELECT partitionDate,
      createdUTC,
      ROW_NUMBER() OVER(PARTITION BY externalid ORDER BY partitionDate, createdUTC DESC NULLS LAST)

the other query:

SELECT partitionDate,
          createdUTC,
          ROW_NUMBER() OVER(PARTITION BY externalid ORDER BY partitionDate DESC, createdUTC DESC NULLS LAST)

Answer

Sorting order should be specified on per column basis with ASC being default, so can be omitted. So, Yes – you should use DESC for each column as in below

SELECT partitionDate,
  createdUTC,
  ROW_NUMBER() OVER(PARTITION BY externalid ORDER BY partitionDate DESC, createdUTC DESC NULLS LAST)

Advertisement

Answer