ORDER BY 1,2... Why you shouldn't use ordinal positions in "order by"
This section describes handy SQL features and functions in BigQuery. This article covers an "order by" best practice.
Editor's note: this post is written by Tamas Ujhelyi—follow him for handy SQL tips on BigQuery.
I used to do this in SQL, but it was just plain wrong.
See, you can order a result set either by ordinal positions (e.g. order by 1, 2) or column names (e.g. order by event_name, event_at).
Guess which one I don't do anymore? 🙃
Yep, ordinal positions.
And there are reasons for that:
1) it's less readable (e.g. order by 1 vs order by id),
2) adding/removing columns in the select breaks your query or gives a different result,
3) you'll also get unexpected results if the column order changes.
Too much potential trouble just to save a few keystrokes.
Editor's note: this is a matter of personal preference though. I (Balazs) use ORDER BY 1, 2, 3... a fair bit, though I agree that the problems listed above do come up, and in production pipelines it is best to refrain from using ordinal positions.
