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

New
jamis
The following is cross-posted from the original Ray Tracer Challenge forum, from a post by garfieldnate. I’m cross-posting it so that the...
New
mikecargal
Title: Hands-On Rust (Chap 8 (Adding a Heads Up Display) It looks like ​.with_simple_console_no_bg​(SCREEN_WIDTH*2, SCREEN_HEIGHT*2...
New
herminiotorres
Hi @Margaret , On page VII the book tells us the example and snippets will be all using Elixir version 1.11 But on page 3 almost the en...
New
raul
Page 28: It implements io.ReaderAt on the store type. Sorry if it’s a dumb question but was the io.ReaderAt supposed to be io.ReadAt? ...
New
alanq
This isn’t directly about the book contents so maybe not the right forum…but in some of the code apps (e.g. turbo/06) it sends a TURBO_ST...
New
leba0495
Hello! Thanks for the great book. I was attempting the Trie (chap 17) exercises and for number 4 the solution provided for the autocorre...
New
fynn
This is as much a suggestion as a question, as a note for others. Locally the SGP30 wasn’t available, so I ordered a SGP40. On page 53, ...
New
nicoatridge
Hi, I have just acquired Michael Fazio’s “Kotlin and Android Development” to learn about game programming for Android. I have a game in p...
New
brunogirin
When I run the coverage example to report on missing lines, I get: pytest --cov=cards --report=term-missing ch7 ERROR: usage: pytest [op...
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’...
1037 19435 386
New
PragmaticBookshelf
Design and develop sophisticated 2D games that are as much fun to make as they are to play. From particle effects and pathfinding to soci...
New
New
AstonJ
You might be thinking we should just ask who’s not using VSCode :joy: however there are some new additions in the space that might give V...
New
AstonJ
In case anyone else is wondering why Ruby 3 doesn’t show when you do asdf list-all ruby :man_facepalming: do this first: asdf plugin-upd...
New
AstonJ
If you are experiencing Rails console using 100% CPU on your dev machine, then updating your development and test gems might fix the issu...
New
AstonJ
Was just curious to see if any were around, found this one: I got 51/100: Not sure if it was meant to buy I am sure at times the b...
New
PragmaticBookshelf
Author Spotlight Jamis Buck @jamis This month, we have the pleasure of spotlighting author Jamis Buck, who has written Mazes for Prog...
New
PragmaticBookshelf
Author Spotlight: Karl Stolley @karlstolley Logic! Rhetoric! Prag! Wow, what a combination. In this spotlight, we sit down with Karl ...
New
PragmaticBookshelf
A concise guide to MySQL 9 database administration, covering fundamental concepts, techniques, and best practices. Neil Smyth MySQL...
New

Sub Categories: