Re:PRQL, Pipelined Relational Query Language
by tgulacsi on Wednesday July 26, 2023 @01:27AM
(#36873467)
This is the "set based" approach for the MAX: there does not exist a bigger element:
SELECT article, dealer, price
FROM shop s1
WHERE NOT EXISTS (SELECT 1 FROM shop s2
WHERE s2.price > s1.price AND
s2.article = s2.article)
ORDER BY article;
Re:PRQL, Pipelined Relational Query Language
by hn_throwaway_99 on Wednesday July 26, 2023 @03:17AM
(#36874114)
Completely agree, thanks for putting it better than I could have, with an excellent example. Correlated subqueries like the example you give, or similarly lateral joins in postgres, fundamentally are treated like for loops by DB engines anyway.
Semi-related, but the example you give is also why I love Postgres' "DISTINCT ON" functionality (I don't know if other DBs have something similar) - it makes it so much easier to reason about these "give me the 'first' one from each group" type queries without having to resort to correlated subqueries.
Re:PRQL, Pipelined Relational Query Language
by imtringued on Wednesday July 26, 2023 @08:03AM
(#36876279)
Unpopular opinion.
The uncorrelated example should have been rewritten with a CTE and should have been aliased as 'article_max_price' as if it was a computed property and where price = amp.price
Re:PRQL, Pipelined Relational Query Language
by slt2021 on Wednesday July 26, 2023 @01:49PM
(#36881876)
queries like these are best suited for window functions, although I am not sure Mysql supports it:
SELECT article, dealer, price FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY article ORDER BY price DESC) as rnk
FROM shop s1
) sub
WHERE sub.rnk=1
ORDER BY article;
this query will be a single pass over table without loops/joins
So let me write it procedurally and have the optimization engine fix it for me, just like how it fixes my SQL.
Even SQL queries are often better understood procedurally. Take this one [1]:
That inner WHERE clause doesn't make sense in my opinion, unless you think of it procedurally as for each row in s1, ask do a search for the highest price amongst all items that share article number.[1] https://dev.mysql.com/doc/refman/8.0/en/example-maximum-colu...