Bonnick: Lucid: First break down the logic you need. Don’t just toss in GROUP BY without knowing what it does.
Walman: Keiter: not at all, I’m simply saying this example is difficult for me to understand and learn from because I’m already struggling with my problem which is a subset of his.
Jarecke: Lucid: It could be that the right approach is to start to understand the logic behind my SQL. I don’t know. But it’s an idea.
Piedra: So is the answer to all of this what I suggested in my original paste, and just precomputing all of the rank data? haha
Martyr: Lucid: If you don’t know how to find the top 2 per group, precomputing rank won’t help with your logic. It’s only help possibly with performance, not the logic.
Difalco: Xgc: select * from Sellers WHERE sellerId=1234 ORDER BY date DESC LIMIT 2;
Lightbody: Lucid: I thought you said you need to do this for multiple sellers.
Grulkey: Xgc: not saying its beautiful or expandable or subquery-ready or performant
Lewinski: Lucid: So you only want to do this for one seller at a time?
Belote: Xgc: sure I could loop that in a horribly slow solution but it would work without requiring finding top 2 per group
Marone: Lucid: Ah. Sure. Loop. Well, that’s the start of an exploding performance problem.
Protasewich: No I want it for multiple sellers at a time but if I precomputed I could simply have a table with that precomputed data sellerId, lastRank, currentRank, change, numberDays
Moerman: Lucid: Most loops in SQL are a sign of the wrong solution. Not all. But most.
Shubrooks: Yeah its an absolutely hideous solution thats why I’ve been working on it 2+ hours :
Barbor: Lucid: So, maybe it’s time to start to learn more SQL, enough to start to understand GROUP BY and my solution. You still may use something slightly different. There are many solutions.
Szekula: I have one possible solution, will make a fiddle for it and see what you think
Barlette: Not sure it’s a good general-case solution but might work for what I want
Tagliarini: Lucid: Sure. For the top 2, there is a more restrictive JOIN approach you can use.
Moreshead: Xgc: I was thinking about yet another subquery to select the date in question for the rank
Firenze: Find the top. Find the next top that is not in the prior result.
Switalski: Xgc: it wouldn’t work if rank were updated multiple times per day but for now I guess it would
Harpool: I’m looking through http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
Flug: Lucid: That’s likely a discussion of “groupwise max”, which is only part of your problem. Getting the next value per group is the complicating factor.
Lawhorn: Xgc: yeah its seeming like that
Dauphin: And my solution is slightly more complicated / general since it handles any N, not just the top 2.
Humm: Groupwise max handles the top 1.
Mautone: Lucid: I have some of these solutions around too.
Kilty: I want to install a front-end for ‘freeradius’. This SQL scheme gives me an error: https://gist.github.com/atmosx/fe643f20e6cd19b1e595#file-userinfo-sql-L5 – id int10 DEFAULT ‘0’ NOT NULL auto_increment comes up with ERROR 1067 42000 at line 4: Invalid default value for ‘id’ . Any idea how to change this in order to make it work?
Rantanen: My version is mysql 5.5 this was probably made for an older version
Danfield: Atmosx_laptop: ist’ auto_increment, doesn’t use a default
Keiter: Atmosx_laptop: remove DEFAULT ‘0’
Wege: Lucid: Here’s one to calculate the sum of the top 5 scores per country. http://sqlfiddle.com/#!2/c6faa/12 It comes with some supporting SQL to understand the calculation and alternative approaches.
Usman: Lucid: None of these problems are trivial, especially without window functions. It requires more than the very basic understanding of SQL.
Woodington: Xgc: yeah I’m just surprised at how complicated they become because the problem seems straightforward. The “Simple” country example in your last paste looks like what I would expect