...
/Antipattern: Reference Non-grouped Columns
Antipattern: Reference Non-grouped Columns
Let’s explore how misconception about the GROUP BY clause causes an antipattern by testing out different queries.
We'll cover the following...
The root cause of this antipattern is simple, and it reveals a common misconception that many programmers have about how grouping queries work in SQL.
The single-value rule
The rows in each group consist only of items that have the same value in the column or columns we name after GROUP BY. For example, in the following query, there is a one-row group for each distinct value in product_id.
Press + to interact
SELECT product_id, MAX(date_reported) AS latestFROM Bugs JOIN BugsProducts USING (bug_id)GROUP BY product_id;
Every column in the select list of a query must have a single-value row per row group. This is called the Single-Value Rule. Columns named in the GROUP BY clause are guaranteed to be exactly one ...