[naasking]

> I feel like a foreigner in another land when I read your comment and others like it. For as long as I can remember using SQL, I can't remember ever finding it more difficult or backwards than anything else I use

Learn linq or query/list comprehensions and then you'll easily see why SQL is backwards.

[Izkata]

I've been using Django almost as long as I've been using SQL and I prefer the SQL ordering more: it matches the rest of the code, making it faster/easier to read. As a crude example:

  SELECT results FROM source WHERE criteria

  results = source(criteria)

It's rare to see someone want to change assignments in code to be like:

  source(criteria) -> results

Where I see it as the same thing: the SELECT columns, like the variable assignment, are the interaction point with the following lines of code.

And yes, CTE ordering does annoy me because of this. Putting it in the middle is pretty much the worst order.

[naasking]

> Where I see it as the same thing: the SELECT columns, like the variable assignment, are the interaction point with the following lines of code.

Indeed, which is why source(criteria) -> results makes more sense: the results definition is right next to the code that's going to be using that definition. If you put the results definition first as with SQL, then you have to scroll up to find the context (although perhaps Python's indentation sensitivity is the tripping point in this case). Not even mentioning the fact that the SQL way completely destroys any chance of code completion.

I'm going to boldly state that the SQL way is literally objectively wrong, in that there is no world in which SQL's choice is superior for general querying.

[Izkata]

Then why are you advocating for it?

> or query/list comprehensions

List comprehensions are column first.

[naasking]

Right, here's the nuance: list comprehensions are intended to be concise one-liners, so having the results definition far off to the right defeats the principle I was outlining. Most SQL queries are not like this, they are almost always multiline of the form:

    select x, y, z
    from Foo
    where a or b

Here the opposite is the case: selection-first moves the return definition far from the subsequent code that uses it.

So if you're going to support list comprehensions, a monadic do-style notation which lets you chain them and again places select last:

https://wiki.haskell.org/List_comprehension#List_monad

[jcparkyn]

Your first example should be more like

  return source(criteria).results

In your SQL, `results` isn't the variable you're assigning to, it's the column you're reading from source.