Hacker News Reader

Comment by PaulHoule | Hacker News Reader

The one that drives me nuts is

   select this,count(*) from that group by this

which is the most common data exploration query of them all which makes you type ‘this’ twice.

View on HN

  select this, count(*) from that group by 1

Tested this out on Postgres and it works:

  SELECT
    name, count(1)
  FROM 
    (VALUES ('Kev',12), ('Meg', 14), ('Kev', 14)) 
  people(name, age)
  GROUP BY
    1

Produces: [[Kev, 2], [Meg, 1]].

But I don't get it. Could someone explain how this works?

Most SQL dialects accept a positional or a direct reference to the column name. “1” represents the first column created by the select statement, “name”.

Ah I see. Is that how `count(1)` also works? Or is that different.

Different. SQL's syntax is funky.

Please never allow this into production. I believe it is deprecated, it certainly is fragile in general.

I actually find this less readable. i have to go back and scan the select to see what column this index corresponds to. Totally breaks the flow of reading.

What would you prefer to see instead, bearing in mind that anything you will suggest is likely to bsave minimal typing but introduce new complexity into sql which everyone agrees is already creaking like a constipated elephant.

How about always returning the "group" column by default. If the user wants to get rid of it later (much less frequent) they can do an outer select or remove the column themselves.

Please give an example.

something like a function or macro

   CREATE MACRO count_values(this, that) WITH DEFINITION
       SELECT this,COUNT(*) FROM that GROUP BY this

Reply on news.ycombinator.com