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

jimmykiang
PragProg Customers

Distributed Services With Go - Agent_test.go error line 77
This test is broken right out of the box… — FAIL: TestAgent (7.82s) agent_test.go:77: Error Trace: agent_test.go:77 agent_test.go:...
#errata/book-distributed-services-with-go
4 1559 7
New
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
iPaul
PragProg Customers

The Definitive ANTLR 4 Reference: Program deprecation warning
page 37 ANTLRInputStream input = new ANTLRInputStream(is); as of ANTLR 4 .8 should be: CharStream stream = CharStreams.fromStream(i...
#errata/book-the-definitive-antlr-4-reference
4 987 0
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
herminiotorres
PragProg Customers

Programming Phoenix LiveView: Typo
Hi! I know not the intentions behind this narrative when called, on page XI: mount() |> handle_event() |> render() but the correc...
#errata/book-programming-phoenix-liveview
3 1307 17
New
frasette
PragProg Customers

A Common-Sense Guide to Data Structures and Algorithms, Second Edition: erroneous extra space complexity (page400)
A Common-Sense Guide to Data Structures and Algorithms, Second Edition by Jay Wengrow @jaywengrow Hi, I have the paperback version of t...
#errata/book-a-common-sense-guide-to-data-structures-and-algorithms-second-edition
4 1180 2
New
adamwoolhether
PragProg Customers

Distributed Services with Go: receiving Unknown flag: --go_opt when compiling protobuf (page 20)
When trying to generate the protobuf .go file, I receive this error: Unknown flag: --go_opt libprotoc 3.12.3 MacOS 11.3.1 Googling ...
/book-distributed-services-with-go
0 4924 3
New
brian-m-ops
PragProg Customers

Python Testing with pytest, Second Edition: cards pip install error (page 12)
#book-python-testing-with-pytest-second-edition Hi. Thanks for writing the book. I am just learning so this might just of been an issue ...
#errata/book-python-testing-with-pytest-second-edition
4 1191 3
New
a.zampa
PragProg Customers

Kotlin and Android Development featuring Jetpack: error concerning function getCurrentStandings in BaseballDao.java
@mfazio23 I’m following the indications of the book and arriver ad chapter 10, but the app cannot be compiled due to an error in the Bas...
#question/book-kotlin-and-android-development-featuring-jetpack
1 3397 6
New
mcpierce
PragProg Customers

Kotlin and Android Development featuring Jetpack: Unique constraint error when running chapter 5 code changes
@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,...
/book-kotlin-and-android-development-featuring-jetpack
0 1180 10
New

Other popular topics Top

PragmaticBookshelf
Backend>Learning Resources

Distributed Services with Go
Take your Go skills to the next level by learning how to design, develop, and deploy a distributed service. Start from the bare essential...
pragprog.com
#pragprog/go#published-book/book-distributed-services-with-go
1 4310 0
New
PragmaticBookshelf
General Dev>Learning Resources

Seven More Languages in Seven Weeks
Learn from the award-winning programming series that inspired the Elixir language, and go on a step-by-step journey through the most impo...
pragprog.com
#pragprog/elixir/julia/lua#published-book#factor/elm#minikanren/idris/book-seven-more-languages-in-seven-weeks
4 5862 0
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
AstonJ
General Dev>Dev Chat

Languages Without Garbage Collection
Continuing the discussion from Thinking about learning Crystal, let’s discuss - I was wondering which languages don’t GC - maybe we can c...
#garbage-collection
21 5575 7
New
foxtrottwist
General Dev>Dev Chat

Warp—The blazingly fast, Rust-based terminal
A few weeks ago I started using Warp a terminal written in rust. Though in it’s current state of development there are a few caveats (tab...
/rust#terminal
52 6785 22
New
First poster: CommunityNews
AI>In The News

How to fix the eyes in AI-generated images
aidemos.info
0 4508 0
New
CommunityNews
General Dev>In The News

Review of Linux on Minisforum V3 AMD Ryzen Tablet
A Brief Review of the Minisforum V3 AMD Tablet. Update: I have created an awesome-minisforum-v3 GitHub repository to list information fo...
mudkip.me
#linux#review#amd
0 4635 0
New
sir.laksmana_wenk
Game Dev>Questions

I want to learn how make a game, but where should I start?
I’m able to do the “artistic” part of game-development; character designing/modeling, music, environment modeling, etc. However, I don’t...
#game-dev
15 4965 9
New
RobertRichards
Game Dev>Chat

Hair Salon Games for Girls Fun
Hair Salon Games for Girls Fun Girls Hair Saloon game is mainly developed for kids. This game allows users to select virtual avatars to ...
#ios#android#unity#fun
2 3556 1
New
mindriot
General Dev>Reviews

Keyboard Review: UHK60V2 vs Defy vs Voyager vs Glove80 vs Svalboard
Ok, well here are some thoughts and opinions on some of the ergonomic keyboards I have, I guess like mini review of each that I use enoug...
/keyboards#uhk60v2#defy#voyager#glove80#svalboard
5 5681 7
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