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

Camelin: Http://sqlfiddle.com/#!9/9e272/3

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.

Fausset: Http://sqlfiddle.com/#!9/9c3e2/7

Trevorrow: What’s the proper fix for this? Do I need to fix my derived tables or do I need to enforce distinct results?