Gillogly: Salle: compare that to what I have, mine is FAR simpler and not terrible in terms of performance
Mercando: Salle: I’m open to anything you believe would be a genuine improvement but I will *only* need the most recent 2 ranks sort by date, get last 2 ranks, and get the difference between the two
Correl: Lucid: Well with GROUP_CONCAT you can easily get top 2 ranks :
Correl: Lucid: Again without any subqueries
Eagleton: Salle: cool I will try to do that now
Bontempo: Salle: would you use IN. Risse for that?
Graeter: Actually I was thinking about getting a pk and doing that but there’s no id
Vanwechel: So I dont know how you’d use GROUP_CONCAT
Garnand: Or rather how you would limit the grouped results
Boldwyn: The “last two” ranks are not necessarily today and yesterday
Huereca: It could be today and yesterday, could be last sa****ay and the sa****ay before, depends on Ranks.date
Altadonna: So how would you form the WHERE clause of the query using GROUP_CONCAT?
Correl: Lucid: Your sqlfiddle data is not good enough because there is no seller with more than one rank to demonstrate it
Mildenhall: Salle: sellers have one rank per day
Correl: Lucid: Yes, but only 2 ranks per seller
Letalien: Salle: all but 1 seller has 2 ranks
Beals: Salle: you want more than 2 ranks per seller? I will add more
Correl: Lucid: So you can’t see what is going on if there are 10 days and 10 rankls
Kibbey: I’m going to make some other changes to the data too so it’s more realistic
Dimitrov: Just depth not breadth
Correl: Lucid: By the way GROUP_CONCAT is MySQL specific and non-standard
Vanderweide: Salle: I’m okay with that but thanks for the heads up
Correl: Lucid: To understand how it works you need to understand how GROUP BY works. Do you?
Beale: Salle: my inexact explanation would be that it groups the rows; if you select cols from the grouped table you get unpredictable results except on the group target or with aggregate functions
Correl: Lucid: GROUP_CONCAT is aggregate function
Sluis: Sure so it’ll basically make it like rank1,rank2
Bakalar: But you have to limit that group to those 2 rows somehow and I dont know how you’d limit the rows included in that group
Lutterman: Otherwise it would give all-time ranks
Leversee: Which right now is 30 but it grows every day, which would eventually be a huge amount of data whole Ranks table of ranks
Esposita: So if I did SELECT GROUP_CONCATrank rankList .GROUP BY sellerId. rankList would become huge
Correl: Lucid: http://sqlfiddle.com/#!9/8da731/29
Abbott: And LIMIT wouldnt apply to GROUP BY
Correl: Lucid: Try to understand how it works :
Correl: Lucid: Add more rows there to see what happens and uncomment WHERE clause if you wish
Ackison: Salle: isn’t it still doing a lot of extra work in the GROUP_CONCAT before truncating it with SUBSTRING_INDEX?
Drowne: Seems like this would be an always-increasing resource sink
Garnand: Good evening guys. I’ve some trouble creating a procedure, and i think it’s something wrong with the delimiter. http://pastebin.com/wz6GZwBN
Smithhart: Could someone have a look on the query?
Balboni: Salle: I’m not a dummy! I just don’t understand the details of how relational databases and more specifically MySQL work :
Cruce: Salle: sometimes I get lost in “which layer” of a subquery I am in but the more I work with them the more familiar I become
Correl: 7GHAA8NOX: It is simpler : Missing name
Correl: Lucid: I never said you are dummy. Not that I see anything wrong about being a dummy with regard of some area of knowledge
Correl: 7GHAA8NOX: Consider normalizing your table.