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.