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

jimschubert
PragProg Customers

Distributed Services With Go - Ch 3 index errors on Config
In Chapter 3, the source for index introduces Config on page 31, followed by more code including tests; Config isn’t introduced until pag...
#pragprog#errata/book-distributed-services-with-go
8 1316 3
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
johnp
PragProg Customers

Python Testing with pytest - Chapter 5 example c AttributeError: 'module' object has no attribute 'config'
Running the examples in chapter 5 c under pytest 5.4.1 causes an AttributeError: ‘module’ object has no attribute ‘config’. In particula...
#errata/book-python-testing-with-pytest
5 3582 1
New
herminiotorres
PragProg Customers

Concurrent Data Processing in Elixir: various errata
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...
#errata/book-concurrent-data-processing-in-elixir
15 1549 13
New
raul
PragProg Customers

Distributed Services with Go: Chapter 1 Suggestions
Hi Travis! Thank you for the cool book! :slight_smile: I made a list of issues and thought I could post them chapter by chapter. I’m rev...
#errata/book-distributed-services-with-go
2 1185 3
New
AleksandrKudashkin
PragProg Customers

Modern Front-End Development for Rails: B7 no main folder in the source code (page xv)
On the page xv there is an instruction to run bin/setup from the main folder. I downloaded the source code today (12/03/21) and can’t see...
#errata/book-modern-front-end-development-for-rails
2 1452 11
New
alanq
PragProg Customers

Modern Front-End Development for Rails: Can't get TURBO_STREAM format in some cases
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...
#question/book-modern-front-end-development-for-rails
1 1386 7
New
brunogirin
PragProg Customers

Python Testing with pytest, Second Edition: error when running tox in parallel (page 151)
When trying to run tox in parallel as explained on page 151, I got the following error: tox: error: argument -p/–parallel: expected one...
#errata/book-python-testing-with-pytest-second-edition
0 1111 1
New
taguniversalmachine
PragProg Customers

Real-Time Phoenix: ProductChannelTest - undefined function describe/2 (pg 203)
Hi, I am getting an error I cannot figure out on my test. I have what I think is the exact code from the book, other than I changed “us...
#question/book-real-time-phoenix
0 1775 6
New
EdBorn
PragProg Customers

Agile Web Development with Rails 7: (page 70)
Title: Agile Web Development with Rails 7: (page 70) I am running windows 11 pro with rails 7.0.3 and ruby 3.1.2p20 (2022-04-12 revision...
#question/book-agile-web-development-with-rails-7
0 1129 1
New

Other popular topics Top

PragmaticBookshelf
Game Dev>Learning Resources

Apple Game Frameworks and Technologies
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...
pragprog.com
#pragprog#ios#game-dev#macos/swift#published-book#apple/book-apple-game-frameworks-and-technologies
30 7995 10
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
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
Backend>Learning Resources

Concurrent Data Processing in Elixir
Learn different ways of writing concurrent code in Elixir and increase your application's performance, without sacrificing scalability or...
pragprog.com
#pragprog/elixir#published-book/book-concurrent-data-processing-in-elixir
78 6059 24
New
Margaret
General Dev>Dev Chat

PragProg’s Medium Posts
Hello everyone! This thread is to tell you about what authors from The Pragmatic Bookshelf are writing on Medium.
#pragprog#blog-post
1147 29994 760
New
PragmaticBookshelf
Backend>Learning Resources

Programming WebRTC
Use WebRTC to build web applications that stream media and data in real time directly from one user to another, all in the browser. ...
pragprog.com
#pragprog#published-book/js#webrtc/book-programming-webrtc
27 6969 6
New
AstonJ
Data Science

Can AI/ML predict a lottery win?
Biggest jackpot ever apparently! :upside_down_face: I don’t (usually) gamble/play the lottery, but working on a program to predict the...
#ai#machine-learning
19 3939 10
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
Backend>Learning Resources

Agile Web Development with Rails 8
Get the comprehensive, insider information you need for Rails 8 with the new edition of this award-winning classic. Sam Ruby @rubys ...
pragprog.com
#pragprog#web-development/ruby/rails#published-book/book-agile-web-development-with-rails-8
12 7404 7
New
AstonJ
AI>Chat

How to: Run DeepSeek on Mac, Windows, and Linux!
This is a very quick guide, you just need to: Download LM Studio: https://lmstudio.ai/ Click on search Type DeepSeek, then select the o...
#macs/deepseek#guides#lm-studio
14 9328 10
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