Ted

Ted

SQL Antipatterns, Volume 1, B4: nudge readers to DISTINCT (page 172)

I’m enjoying the book and I’m happy to see that the discussion of GROUP BY in chapter 15 also includes a callout box titled “GROUP BY and DISTINCT”.

I’d like to suggest that the callout box nudge readers in the direction of DISTINCT over GROUP BY for the situation it describes.

The callout box compares two different queries:

SELECT DISTINCT date_reported, reported_by
FROM Bugs;
SELECT date_reported, reported_by
FROM Bugs
GROUP BY date_reported, reported_by;

The callout box concludes with:

Both queries produce the same result and should be optimized and executed similarly, so the difference in this example is only a matter of preference.

Comparing the two queries is indeed valuable, but I suggest adding a bit more language, like this:

Both queries produce the same result and should be optimized and executed similarly, so the difference in this example is only a matter of preference. With all else being equal, using DISTINCT has the advantage of communicating the intention more clearly.

My experience might be skewed, but I immediately know what I’m looking at when I see SELECT DISTINCT.

On the other hand, encountering a GROUP BY without any aggregation would make me wonder if I just found a bug.

This small way of increasing code clarity seems like another opportunity to avoid pitfalls.

Marked As Solved

billkarwin

billkarwin

Author of SQL Antipatterns, Volume 1

Thanks Ted! I like the suggestion. I’ll add some phrasing.

Where Next?

Popular Pragmatic Bookshelf topics Top

jimschubert
In Chapter 3, the source for index introduces Config on page 31, followed by more code including tests; Config isn’t introduced until pag...
New
telemachus
Python Testing With Pytest - Chapter 2, warnings for “unregistered custom marks” While running the smoke tests in Chapter 2, I get these...
New
brianokken
Many tasks_proj/tests directories exist in chapters 2, 3, 5 that have tests that use the custom markers smoke and get, which are not decl...
New
Alexandr
Hi everyone! There is an error on the page 71 in the book “Programming machine learning from coding to depp learning” P. Perrotta. You c...
New
joepstender
The generated iex result below should list products instead of product for the metadata. (page 67) iex> product = %Product{} %Pento....
New
curtosis
Running mix deps.get in the sensor_hub directory fails with the following error: ** (Mix) No SSH public keys found in ~/.ssh. An ssh aut...
New
brunogirin
When trying to run tox in parallel as explained on page 151, I got the following error: tox: error: argument -p/–parallel: expected one...
New
hazardco
On page 78 the following code appears: <%= link_to ‘Destroy’, product, class: ‘hover:underline’, method: :delete, data: { confirm...
New
mcpierce
@mfazio23 I’ve applied the changes from Chapter 5 of the book and everything builds correctly and runs. But, when I try to start a game,...
New
dachristenson
@mfazio23 Android Studio will not accept anything I do when trying to use the Transformations class, as described on pp. 140-141. Googl...
New

Other popular topics Top

Devtalk
Reading something? Working on something? Planning something? Changing jobs even!? If you’re up for sharing, please let us know what you’...
1023 17214 380
New
Exadra37
Please tell us what is your preferred monitor setup for programming(not gaming) and why you have chosen it. Does your monitor have eye p...
New
AstonJ
SpaceVim seems to be gaining in features and popularity and I just wondered how it compares with SpaceMacs in 2020 - anyone have any thou...
New
brentjanderson
Bought the Moonlander mechanical keyboard. Cherry Brown MX switches. Arms and wrists have been hurting enough that it’s time I did someth...
New
AstonJ
Curious to know which languages and frameworks you’re all thinking about learning next :upside_down_face: Perhaps if there’s enough peop...
New
Rainer
My first contact with Erlang was about 2 years ago when I used RabbitMQ, which is written in Erlang, for my job. This made me curious and...
New
AstonJ
We have a thread about the keyboards we have, but what about nice keyboards we come across that we want? If you have seen any that look n...
New
mafinar
Crystal recently reached version 1. I had been following it for awhile but never got to really learn it. Most languages I picked up out o...
New
rustkas
Intensively researching Erlang books and additional resources on it, I have found that the topic of using Regular Expressions is either c...
New
AstonJ
If you get Can't find emacs in your PATH when trying to install Doom Emacs on your Mac you… just… need to install Emacs first! :lol: bre...
New

Sub Categories: