5 ms·

Most of these are “play stupid games, win stupid prices”, not things you’d encounter in normal usage. Queries like `select a, b from nums order by 2` are nonse

• anamexis 7y ago
  Some of those queries are nonsense, but `select a, b from nums order by 2` is not one of them. `order by 2` here means "order by the second field in the select statement."
  • Areading314 7y ago
    No, it is using the constant expression 2 as a sort key, resulting in a random order which should be expected Edit -- this is wrong see below
    • benesch 7y ago
      That is not correct. The parent has it right. The output is in fact sorted by the second field; it is not random. > Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values. > The ordinal number refers to the ordinal (left-to-right) position of the output column. This feature makes it possible to define an ordering on the basis of a column that does not have a unique name. This is never absolutely necessary because it is always possible to assign a name to an output column using the AS clause. (emphasis mine) https://www.postgresql.org/docs/current/sql-select.html#SQL-ORDERBY https://www.postgresql.org/docs/current/sql-select.html#SQL-...
      • Areading314 7y ago
        Interesting -- didn't know it did this
        • MaxGabriel 7y ago
          One way I've seen it used is when you need to order by all or nearly all of the columns in a select statement. Sometimes select a.1, a.2, a.3, ... a.10 GROUP BY 1,2,3,4,5,6,7,8,9,10 conveys the intent more clearly than listing out all the column names
          • sk5t 7y ago
            It is also very helpful to avoid re-typing expressions. select a || 'baz', avg(b) from foo group by 1 order by 2;
      • grecy 7y ago
        could '2' be a column name? ... meaning 'order by 2' would order it by the values in the column named '2', but which wasn't selected.
        • benesch 7y ago
          In PostgreSQL, the grammar does not permit unquoted identifiers to start with a digit. So this is a syntax error: SELECT 'val' AS 2 Instead you must escape the identifier with double quotes: SELECT 'val' AS "2" So there is no ambiguity on that front in the ORDER BY clause. Either you have `ORDER BY 2`, which means order by the second column, or you have `ORDER BY "2"`, which means order by the column named "2".
      • adamzochowski 7y ago
        Some SQL Servers, like MS-SQL have been slowly going into banning order by a number / constant expression, and flagging column numbers as error prone constructs. https://www.sql-server-performance.com/error408/ https://www.sql-server-performance.com/error408/
  • deleted 7y ago
    [deleted]
  • progre 7y ago
    One note: While using field number for anything is perfecrly valid SQL, this will not pass a code review if I'm the one doing the review. Reason being, it's not clear without looking at the field list what is being ordered. For extra shame do something like: select * from foo order by 2 Order of the fields when doing "select *" may be guaranteed (I don't know) but they have been dependent on when the feild was added to the database on every system I have worked with, and sometimes patches are applied in diffrent orders on diffrent databases.
    • plq 7y ago
      If you have a Python script create your tables AND 1=1 AND this script has the table information stored in dicts in the {column name: column type} form AND this script is run by a python version where the dict order is undefined you will get funny outputs from your queries that break production deployments in subtle and unique ways. So yeah, just use column names (and name your computed columns!).
      • progre 7y ago
        Yes, name your computed columns. Makes your life easier.
• dan-robertson 7y ago
  Right but if you’re trying to be Postgres compatible then you have to care and if you are making a compiler off some sort which outputs sql then you have to care a lot because compiler often generates things that are not natural
  • jamii 7y ago
    Exactly. We're often on both ends of this - some BI tool will generate a SQL expression that a human would never have typed, and we have to handle it the same way that postgres would.
• csours 7y ago
  It's not nonsense, but it is a bit of a footgun if you put it into production. Order by 2 is the kind of thing I use all the time in investigations and POCs, but if you put it into prod, you have to account for someone else updating the select and getting things out of order.