3 ms·

In one interview I had, I was asked, "What are you good at?". I said, "Pretty good with SQL stuff". This was _literally_ the question they asked. It was a go

In one interview I had, I was asked, "What are you good at?". I said, "Pretty good with SQL stuff". This was _literally_ the question they asked. It was a good jumping off point for them to probe how much I knew. I like this article's explanation. The thing that trips me up on specific databases, is whether aliases assigned in the "select" portion are available in the "group by" sections.

cm2187 5y ago
Are there databases where the alias defined in the select part of the query is available in the group by/having section?
- simondotau 5y ago
  MySQL/MariaDB certainly permits their use in GROUP BY, HAVING and ORDER BY.
- MrDOS 5y ago
  Postgres reference SELECTed aliases in ORDER BY/GROUP BY clauses, but _not_ in HAVING: test=# create table event (who text not null, test(# what text not null, test(# at timestamptz not null default now()); CREATE TABLE test=# insert into event (who, what) test-# values ('Bob', 'sent a message'), test-# ('Alice', 'received a message'), test-# ('Alice', 'sent a message receipt'), test-# ('Bob', 'received the message receipt'), test-# ('Bob', 'waited patiently for a response'), test-# ('Bob', 'worried whether his message had been well-received'), test-# ('Bob', 'paced'), test-# ('Alice', 'forgot to reply for two weeks'), test-# ('Bob', 'began to panic'), test-# ('Bob', 'fled the country for Moldova in a fit of panic'), test-# ('Alice', 'finally remembered to reply'), test-# ('Bob', 'never got it'); INSERT 0 12 test=# select who as "The poor sod", test-# count(what) as "Events" test-# from event test-# group by "The poor sod" test-# order by "Events" desc; The poor sod | Events --------------+-------- Bob | 8 Alice | 4 (2 rows)
- jameshart 5y ago
  Really varies by implementation. Snowflake supports it - https://docs.snowflake.com/en/sql-reference/constructs/having.html https://docs.snowflake.com/en/sql-reference/constructs/havin... Redshift doesn’t: https://docs.aws.amazon.com/redshift/latest/dg/r_HAVING_clause.html https://docs.aws.amazon.com/redshift/latest/dg/r_HAVING_clau... Databricks doesn’t: https://docs.databricks.com/sql/language-manual/sql-ref-syntax-qry-select-having.html https://docs.databricks.com/sql/language-manual/sql-ref-synt... All of them do also support teradata’s QUALIFY clause which is like another layer over WHERE and HAVING that filters windowed data.
code_biologist 5y ago
I always hate that aliases thing — after seeing query builders do it I've taken to using column numbers (eg. GROUP BY 1, 2, 3) in my own code if I'm grouping on a lot of computed columns. More foolproof. SQL is not a great language. Serviceable, but not great.
- mwexler 5y ago
  Foolproof til you restructure your select and forget to change the numbers. SQL: ya just can't win sometimes.
  - tedeh 5y ago
    To be fair you will have the same problem if you ever change the name of the alias.
em500 5y ago
It's not available in SELECT (barring some non-standard implementations). That's because SELECT is executed after FROM, WHERE, GROUP BY and HAVING. I've made it a habit to start composing my queries in the logical order first, and rearranging it in my editor afterwards. I learned this (and most of my understanding of SQL) from https://blog.jooq.org/10-easy-steps-to-a-complete-understanding-of-sql/ https://blog.jooq.org/10-easy-steps-to-a-complete-understand... (point 2.), which was recommended by Hadley Wickham (of R tidyverse/dplyr fame).

cm2187 5y ago

Are there databases where the alias defined in the select part of the query is available in the group by/having section?

simondotau 5y ago
MySQL/MariaDB certainly permits their use in GROUP BY, HAVING and ORDER BY.
MrDOS 5y ago
Postgres reference SELECTed aliases in ORDER BY/GROUP BY clauses, but _not_ in HAVING: test=# create table event (who text not null, test(# what text not null, test(# at timestamptz not null default now()); CREATE TABLE test=# insert into event (who, what) test-# values ('Bob', 'sent a message'), test-# ('Alice', 'received a message'), test-# ('Alice', 'sent a message receipt'), test-# ('Bob', 'received the message receipt'), test-# ('Bob', 'waited patiently for a response'), test-# ('Bob', 'worried whether his message had been well-received'), test-# ('Bob', 'paced'), test-# ('Alice', 'forgot to reply for two weeks'), test-# ('Bob', 'began to panic'), test-# ('Bob', 'fled the country for Moldova in a fit of panic'), test-# ('Alice', 'finally remembered to reply'), test-# ('Bob', 'never got it'); INSERT 0 12 test=# select who as "The poor sod", test-# count(what) as "Events" test-# from event test-# group by "The poor sod" test-# order by "Events" desc; The poor sod | Events --------------+-------- Bob | 8 Alice | 4 (2 rows)
jameshart 5y ago
Really varies by implementation. Snowflake supports it - https://docs.snowflake.com/en/sql-reference/constructs/having.html https://docs.snowflake.com/en/sql-reference/constructs/havin... Redshift doesn’t: https://docs.aws.amazon.com/redshift/latest/dg/r_HAVING_clause.html https://docs.aws.amazon.com/redshift/latest/dg/r_HAVING_clau... Databricks doesn’t: https://docs.databricks.com/sql/language-manual/sql-ref-syntax-qry-select-having.html https://docs.databricks.com/sql/language-manual/sql-ref-synt... All of them do also support teradata’s QUALIFY clause which is like another layer over WHERE and HAVING that filters windowed data.

code_biologist 5y ago

I always hate that aliases thing — after seeing query builders do it I've taken to using column numbers (eg. GROUP BY 1, 2, 3) in my own code if I'm grouping on a lot of computed columns. More foolproof. SQL is not a great language. Serviceable, but not great.

mwexler 5y ago
Foolproof til you restructure your select and forget to change the numbers. SQL: ya just can't win sometimes.
- tedeh 5y ago
  To be fair you will have the same problem if you ever change the name of the alias.

em500 5y ago

It's not available in SELECT (barring some non-standard implementations). That's because SELECT is executed after FROM, WHERE, GROUP BY and HAVING. I've made it a habit to start composing my queries in the logical order first, and rearranging it in my editor afterwards. I learned this (and most of my understanding of SQL) from https://blog.jooq.org/10-easy-steps-to-a-complete-understanding-of-sql/ https://blog.jooq.org/10-easy-steps-to-a-complete-understand... (point 2.), which was recommended by Hadley Wickham (of R tidyverse/dplyr fame).