Marrietta: Lucid: It can get very complicated quickly without certain standard features, like window functions.
Derhammer: Or CTE and recursive support.
Gobbi: Xgc: I know discussion channels aren’t exactly in the habit of plugging other packages but.
Lepage: Lucid: PG, Oracle, have these.
Shierling: Lucid: SQL Server has some as well.
Schaedler: Lucid: I use Oracle in my daily work. So I only need to explain the more complicated approaches here with MySQL.
Echavarria: Well this whole thing makes me incredibly sad
Crockwell: Even correct solutions look insane
Abke: Lucid: Sad is more when you have no solution available, not when the solution is a little over your head. In the last case, you just need to study a little more.
Migliaccio: I wanted to learn how to do it right and make it an elegant solution though
Maisch: Something relatively speaking simple
Stringari: Lucid: If you get over that hurdle, you’ll be in a much better place.
Clyman: Lucid: It’s still helpful to understand the various ways to solve a problem.
Veltkamp: Xgc: For sure, but it seems like I have a bunch of suboptimal solutions
Keiter: Lucid: what is your optimal solution?
Handing: Lucid: Don’t let the complexity trun you off. and . never worry about performance before you are ready. You’ll almost always end up in a very bad place, avoiding the right relatively speaking solutions because you weren’t ready when you decided to LOOP over everything,
Crombie: Xgc: write a 50-line abomination of a query, write a query which isn’t 50 lines but isn’t performant, precompute everything in a loop, add something to to the tech stack
Sanker: Keiter: I’m not sure, seems like the long climb and the 50-line query is as close as I’ll get but I’m miles away from being able to do that
Clingan: Lucid: I have single queries that are several hundred lines long. The size of the query is never a factor to me.
Stolp: Learning something like this with many components is challenging because when I don’t get the expected result, it’s hard to tell why that’s the case
Bensberg: Lucid: In production.
Keiter: Lucid: salle gave you a simple fiddle that showed the issue with incorrect aggregation.
Farquer: Lucid: Learn step by step. Don’t try to handle it all at once.
Storr: Keiter: doing it wrong isnt the problem, finding out how to do it right is
Tewmey: Lucid: You’re missing too many pieces at the moment. Understanding GROUP BY is a good start. Don’t try to learn it in the context of your harder problem. That might just be too confusing when the fundamental GROUP BY lesson is much simpler.
Yearsley: It’s ok to use your tables. But don’t keep trying to learn GROUP BY in your query to solve the Top N per Group question. That’s going to slow you down.
Mehaffy: Xgc: I understand most of GROUP BY, I just misunderstood how it worked with LIMIT
Hort: Lucid: LIMIT has nothing to do with it. LIMIT is after GROUP BU.
Sok: Xgc: right, I wanted to limit what got grouped
Stoey: Lucid: That’s where you could use a derived table before GROUP BY.
Bowron: Xgc: because if I limited it to precisely the two rows I wanted, I could use MIN and MAX to reference the two values
Ferreyra: Lucid: That brings up another problem, however.
Hasley: Xgc: what is that problem?
Shoe: Lucid: It’s the “per group” logic that is the problem. Using the MIN / MAX is trivial once you have the 2 rows per group.
Meconi: Lucid: You just can’t use LIMIT for this at all.
Dituri: Lucid: LIMIT has no ability to work across groups.
Cattaneo: Xgc: Okay, but why? For example select rank from ranks order by date desc limit 2?
Noori: Lucid: That will return upto 2 rows only.
Melville: Not 2 rows per group.
Plumley: Lucid: You might be able to use correlated behavior, however.