← Back to context

Comment by Blackthorn

1 year ago

FROM first would be nothing short of incredible. I can only hope that Postgres and others can find it within themselves to get together and standardize on such an extension!

9 comments

Blackthorn

Reply
willvarfar  1 year ago

Yeap I didn't know DuckDB supported it already!

Being able to do SELECT FROM WHERE in any order and allowing multiple WHEREs and AGGREGATE etc, combined with supporting trailing commas, makes copy pasting templating and reusing and code-generating SQL so much easier.

  FROM table  <-- at this point there is an implicit SELECT *
  SELECT whatever
  WHERE some_filter
  WHERE another_filter <-- this is like AND
  AGGREGATE something
  WHERE a_filter_that_is_after_grouping <-- is like HAVING
  ORDER BY ALL <-- group-by-all is great in engines that support it; want it for ordering too

...

  • croes  1 year ago

    A special keyword like HAVING prevents erros by typing in the wrong line.

    How is OR done with this WHERES?

  • aidos  1 year ago

    What’s group-by-all? Sounds like distinct?

    • genezeta  1 year ago

      It's different from distinct. Distinct just eliminates duplicates but does not group entries.

      Suppose...

        SELECT brand, model, revision, SUM(quantity)
   FROM stock
   GROUP BY brand, model, revision

      This is not solved by using distinct as you would not get the correct count.

      Group By All allows you to write it a bit more compact...

        SELECT brand, model, revision, SUM(quantity)
   FROM stock
   GROUP BY ALL

      1 reply →

    • willvarfar  1 year ago

      Normally the SELECT has a bunch of columns to group by and a bunch of columns that are aggregates. Then, in the GROUP BY clause, you have to list all the columns to group by. The query compiler knows which they are, and polices you, making sure you got it right. All the GROUP BY ALL does is say 'the compiler knows, there's no need to list them all'. Very convenient.

      BigQuery supports GROUP BY ALL and it really cleans up lots of queries. E.g.

         SELECT foo, bar, SUM(baz)
   FROM x
   GROUP BY ALL <-- equiv to GROUP BY foo, bar

      (eh, except MySQL; my memory of MySQL is it will silently do ANY_VALUE() on any columns that aren't an explicit aggregate function but are not grouped; argh it was a long time ago)

      1 reply →

quartesixte  1 year ago

What exactly is the history of having FROM be the second item, and not the first? Because FROM first seems more intuitive and actually the way you write out queries.

Really hope this takes off and gets more widespread adoption because I really want to stop doing:

  SELECT *
  FROM all_the_joins

into

  SELECT {my statements here}
  FROM all_the_joins