0

ORDER BY WITH FILL doesn't work if the result is empty:

SELECT val
FROM table
WHERE 1 != 1
ORDER BY val WITH FILL FROM 1 TO 5

Ok.
0 rows in set. Elapsed: 0.173 sec.

Most probably because ORDER BY is not involved in execution here.

Is there a way to make work, or the only workaround is to use UNION like described here?

tetafro
  • 477
  • 1
  • 10
  • 15

1 Answers1

2

You can do a mix of both. Union all with a default value you can filter at the end and the FILL clause:

SELECT * FROM (
  SELECT *
  FROM
  (
    SELECT 0 AS number
    UNION ALL
    SELECT number
    FROM numbers(10)
    WHERE 1 != 1
  )
  ORDER BY number ASC WITH FILL FROM 1 TO 5
)
WHERE number != 0

┌─number─┐
│      1 │
│      2 │
│      3 │
│      4 │
└────────┘
alrocar
  • 178
  • 1
  • 6