Tyrrell: Keiter: I’m interested in the right best way, is that the best way?
Keiter: Lucid: if you want the first and last rank, I would join a single derived table instead.
Keiter: Lucid: FROM . JOIN SELECT user, MAXrk AS max_rk, MINrk AS min_rk FROM . GROUP BY user AS b ON .
Biren: Keiter: I don’t know how I would “rotate” the resultset like that; the two rank values would be in rows but if they were both in the same result they’d need to be columns of that derived table no?
Keiter: Lucid: the aliases from the derived table are available to the outer scope.
Mower: Okay but I don’t think I can use aggregate functions on a group like that
Keiter: Lucid: LIMIT is the wrong answer.
Borok: WHERE date=CURDATE OR date=CURDATE – INTERVAL 1 DAY?
Keiter: Lucid: build your WHERE clause as you wish
Keiter: Lucid: I have no idea what your table structure looks like, so you’ve yet to define the problem appropriately.
Keiter: Lucid: I was initially commenting on some of the mistakes salle pointed out
Pellom: Keiter: that’s fine and much appreciated
Beschorner: Keiter: I do want the last two ranks DESC by date but it might not be today and yesterday, which is what makes it difficult
Keiter: Lucid: then you can use two derived tables, perhaps.
Housewright: Keiter: I don’t want to beat on the 3rd party site so I limit my scraping on a daily basis, but do more comprehensive scan on their slowest day to include more people
Keiter: Lucid: if you build a test case on sqlfiddle.com, it would be easier to continue
Prowse: Lucid: That kind of problem is solved more directly with window functions row_number, rank, dense_rank, etc. MySQL doesn’t have this. You can do this indirectly without window function support in various ways.
Genous: At least for the more trivial window function cases. The more complex cases are just not practically done without window functions.
Pregler: Lucid: Creating that testcase in sqlfiddle.com as suggested above will be helpful.
Wesby: Xgc: yeah I am working on it, not familiar with this tool or sharing schema so I am checking out the sqlfiddle exampes
Zais: This is just the beginning of my rewrite but the schema is there so you can see it
Weldy: Why isn’t any index being used in this? http://fpaste.org/269491/42769581/ Here’s the schema https://github.com/wikimedia/mediawiki-extensions-CentralAuth/blob/master/central-auth.sql#L195-L235
Persaud: Lucid: What specific question do you wish to focus on now?
Helble: Lucid: You asked many questions.
Vanderzwaag: Xgc: my #1 issue is how to find the last 2 ranks without violating any of this
Uyehara: Xgc: if the last 2 ranks were reliably “today” and “yesterday” that’d be fine but neither is true
Mulherin: Lucid: Sorry. Violating what?
Moro: Xgc: SELECT * . GROUP BY. or dependent subqueries or something terrible and slow
Thuotte: Xgc: let me make an example of a record which would be a problem
Boursiquot: Lucid: Finding the last 2 of some ordered list per group is a general rank issue. Have you looked at articles that describe finding the ‘Top N per Group’? I’ve posted one or two solutions over the years.
Countess: Lucid: It’s directly solved using window functions, but MySQL does not have this.
Struthers: Lucid: Just add data to your fiddle and your current attempt to find the last 2 per groul.
Barbier: Lucid: There are many ways to solve the general problem: http://sqlfiddle.com/#!2/464f0/7
Shirakawa: Lucid: When you only care about the top 2 per group, you can just use the more limited join approach.
Bongartz: Where each table in the join is used to find one of the pair.
Trevorrow: What’s the proper fix for this? Do I need to fix my derived tables or do I need to enforce distinct results?