PragProg Customers

SQL Antipatterns, Volume 1: join error (page 176)

blabla_bingo

blabla_bingo

SQL Antipatterns, Volume 1: join error (page 176)

@billkarwin

On page 176 , the derived-table-no-duplicates.sql code would not get the correct result if multiple product_id with the same latest date_reported value exist.

For example, product_id 2 has a bug_id 3456 and the latest reported_date is ‘2010-02-16’. But if product_id 3 has a bug_id 5999 with the same latest reported_date ‘2010-02-16’, then the query will get a wrong bug_id for product_id 2.

1 comment
#errata/book-sql-antipatterns-volume-1
0 228 1

First Post!

billkarwin

billkarwin

Author of SQL Antipatterns, Volume 1

Thanks for the feedback blabla_bingo, I see the error. Yes, this is a mistake.

Here’s a working query for that example:


SELECT m.product_id, m.latest, MAX(b1.bug_id) AS latest_bug_id
FROM Bugs b1
JOIN BugsProducts p USING (bug_id)
JOIN (
  SELECT product_id, MAX(date_reported) AS latest
   FROM Bugs b2 JOIN BugsProducts USING (bug_id)
   GROUP BY product_id
) m
  ON (b1.date_reported = m.latest AND p.product_id = m.product_id)
GROUP BY m.product_id, m.latest;

I’ll try to get this correction into the next printing of the book.

Post #2

Where Next?

View thread on forum
View Sql Antipatterns Volume 1's book portal
Post errata for this book
Post a suggestion for this book
Post a question about this book
Post a review about this book
Home PragProg Customers
#errata/book-sql-antipatterns-volume-1
0 228 1
Last post

Popular Pragmatic Bookshelf topics Top

duke_meister
PragProg Customers

The Ray Tracer Challenge - where are the errata?
As per the title, thanks.
/book-the-ray-tracer-challenge
6 1681 4
New
jon
PragProg Customers

The Pragmatic Programmer, 20th Anniversary Edition errata
Some minor things in the paper edition that says “3 2020” on the title page verso, not mentioned in the book’s errata online: p. 186 But...
#errata/book-the-pragmatic-programmer-20th-anniversary-edition
10 2636 9
New
ianwillie
PragProg Customers

Build Websites with Hugo: Code will not run in a working Hugo setup
Hello Brian, I have some problems with running the code in your book. I like the style of the book very much and I have learnt a lot as...
#question/book-build-websites-with-hugo
0 1501 3
New
jamis
PragProg Customers

The Ray Tracer Challenge: various errata
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...
#errata/book-the-ray-tracer-challenge
4 2434 3
New
lirux
PragProg Customers

The Ray Tracer Challenge: "Computing the normal on a transformed sphere" ebook test
Hi Jamis, I think there’s an issue with a test on chapter 6. I own the ebook, version P1.0 Feb. 2019. This test doesn’t pass for me: ...
#errata/book-the-ray-tracer-challenge
10 1461 5
New
jdufour
PragProg Customers

The Cucumber for Java Book:
Hello! On page xix of the preface, it says there is a community forum "… for help if your’re stuck on one of the exercises in this book… ...
#question/book-the-cucumber-for-java-book
3 1143 1
New
fynn
PragProg Customers

Build a Weather Station with Elixir and Nerves: SGP30 vs SGP40
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, ...
#suggestion/book-build-a-weather-station-with-elixir-and-nerves
0 1292 2
New
jskubick
PragProg Customers

Kotlin and Android Development featuring Jetpack: SwitchCompat's thumbTint and trackTint being ignored (page 76)
I think I might have found a problem involving SwitchCompat, thumbTint, and trackTint. As entered, the SwitchCompat changes color to hol...
#errata/book-kotlin-and-android-development-featuring-jetpack
0 2749 2
New
creminology
PragProg Customers

Real Time Phoenix Errata P17: Getting first sample working with Phoenix 1.6.x
Skimming ahead, much of the following is explained in Chapter 3, but new readers (like me!) will hit a roadblock in Chapter 2 with their ...
#errata/book-real-time-phoenix
6 1116 5
New
Keton
PragProg Customers

Hands-on Rust: How do I debug a call chain in Rust?
When running the program in chapter 8, “Implementing Combat”, the printout Health before attack was never printed so I assumed something ...
#question/book-hands-on-rust
2 1034 2
New

Other popular topics Top

PragmaticBookshelf
General Dev>Learning Resources

A Common-Sense Guide to Data Structures and Algorithms, Second Edition
Algorithms and data structures are much more than abstract concepts. Mastering them enables you to write code that runs faster and more e...
pragprog.com
#pragprog/python/ruby#published-book/book-a-common-sense-guide-to-data-structures-and-algorithms-second-edition#math#algorithms/js
19 6022 5
New
DevotionGeo
Backend>Chat

Would you use Erlang now when there is Elixir?
Why, if your answer is yes?
/elixir/erlang
167 4955 52
New
Rainer
Community>Journals

Programming Erlang Book Club
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...
/erlang/book-programming-erlang-2nd-edition#book-club
195 6815 95
New
AstonJ
General Dev>Dev Chat

How fast do you type? Check your WPM here!
Do the test and post your score :nerd_face: :keyboard: If possible, please add info such as the keyboard you’re using, the layout (Qw...
typing-speed-test.aoeu.eu
/keyboards
82 7682 31
New
Exadra37
Linux>Chat

RancherOS is in end of life
Oh just spent so much time on this to discover now that RancherOS is in end of life but Rancher is refusing to mark the Github repo as su...
#linux#rancheros
10 6358 6
New
PragmaticBookshelf
Community>In The Spotlight

Spotlight: Mike Riley (Author) Interview and AMA!
Author Spotlight Mike Riley @mriley This month, we turn the spotlight on Mike Riley, author of Portable Python Projects. Mike’s book ...
#author-spotlight/python#iot/book-portable-python-projects#internet-of-things
62 7035 19
New
PragmaticBookshelf
Community>In The Spotlight

Spotlight: VM Brasseur (Author) Interview and AMA!
Author Spotlight: VM Brasseur @vmbrasseur We have a treat for you today! We turn the spotlight onto Open Source as we sit down with V...
#author-spotlight/book-forge-your-future-with-open-source
16 5051 11
New
PragmaticBookshelf
Community>In The Spotlight

Spotlight: Bruce Tate (Author) Interview and AMA!
Author Spotlight: Bruce Tate @redrapids Programming languages always emerge out of need, and if that’s not always true, they’re defin...
/elixir/ruby/phoenix/book-seven-more-languages-in-seven-weeks/book-seven-languages-in-seven-weeks#liveview/book-programming-phoenix-liveview
54 5678 23
New
AstonJ
Backend>Questions

Psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
If you’re getting errors like this: psql: error: connection to server on socket “/tmp/.s.PGSQL.5432” failed: No such file or directory ...
#macos/rails/postgresql
1 5553 1
New
Fl4m3Ph03n1x
AI>Questions

What are the best text-to-speech ai generation tools that you can run locally?
Background Lately I am in a quest to find a good quality TTS ai generation tool to run locally in order to create audio for some videos I...
#ai#text-to-speech
6 8411 3
New
Back to top

Latest in SQL Antipatterns, Volume 1

SQL Antipatterns, Volume 1 Portal ❯

PragProg Customers

Pragmatic Bookshelf PragProg Customers ❯

Latest on Devtalk

Devtalk ❯

We ❤️ helpful members!

We reward our most helpful members via our MOTM scheme - by giving away a whopping 25 books per year!

Member of the Month

Devtalk Sponsors

Pragmatic Bookshelf

Practical resources that improve the lives of professional developers.

Erlang Solutions

We build trusted fault-tolerant systems that scale to billions of users.

Honeybadger

Real-time error tracking, performance insights, and observability for devs.

EEF

Supporting innovation across the BEAM ecosystem.

coders51

We build reliable cloud platforms for business-critical systems.

Manning Publications

Develop your skills with books, videos, and courses.

AppSignal

Catch errors, track performance, monitor hosts and more.

WyeWorks

Enabling companies to succeed by building software people love.

Pragmatic Studio

Courses that move you from confusion to "Aha, now I get it!"

Linux New Media

Publishing Linux Magazine, ADMIN, and MakerSpace for 25+ years.

View all our sponsors ❯

Categories:

Sub Categories:

Popular Portals

Devtalk Sponsors

Pragmatic Bookshelf

Practical resources that improve the lives of professional developers.

Erlang Solutions

We build trusted fault-tolerant systems that scale to billions of users.

Honeybadger

Real-time error tracking, performance insights, and observability for devs.

EEF

Supporting innovation across the BEAM ecosystem.

coders51

We build reliable cloud platforms for business-critical systems.

Manning Publications

Develop your skills with books, videos, and courses.

AppSignal

Catch errors, track performance, monitor hosts and more.

WyeWorks

Enabling companies to succeed by building software people love.

Pragmatic Studio

Courses that move you from confusion to "Aha, now I get it!"

Linux New Media

Publishing Linux Magazine, ADMIN, and MakerSpace for 25+ years.

View all our sponsors ❯

We're in Beta

About us Mission Statement See our Roadmap