Ruffing: Juxta: How many of these max do you expect?
Malbon: Xgc: ideally upto 500, but can do smaller queries if this is unrealistic
Karmely: Juxta: That’s not too nice. SQL is not meant to vary in width of result.
Szczesny: Xgc: yeah, indeed. I could start with a more simple query, say 10 items
Prow: Juxta: You could attempt to join for each item, once for each column. You could do some of this in a procedure.
Hemmes: Juxta: That would be up to ~500 joins. The other approach is GROUP_CONCAT, which may be a problem with that many items in the group.
Neumeyer: Https://www.percona.com/blog/2014/05/23/improve-innodb-performance-write-bound-loads/ anyone know if this is sane? Someone mentioned that they recall a rebuttal, somewhere, but I haven’t been able to find it through googling.
Linker: Juxta: How many dates do you expect?
Mcrary: Xgc: probably about 5000 dates
Ogen: Juxta: SQL isn’t very good for direct report building, unless you resort to procedures.
Wilmott: Juxta: The simplest would be to try GROUP_CONCAT. Test the limitations. 500 wide is large.
Mcardell: Xgc: yeah. I think I might just write a bit of Python to achieve it :
Cheli: Xgc: thanks for all your help
Kays: Juxta: SELECT date, GROUP_CONCATbuild item result here FROM . GROUP BY date;
Huski: Juxta: Simple, but with limitations.
Malotte: Juxta: The expression inside the GROUP_CONCAT will be fairly simple. You just need to provide enough format to satisfy your app. The date could be included in that group_concat result string.
Fujimoto: CONCATdate, ‘, ‘, GROUP_CONCAT. AS result_row .
Axelson: Juxta: The join approach will be really ugly and you’ll need to build the SQL statement dynamically based on the number of items in the list.
Connington: Is this not appropriate: select col1 from table1 where col2 is null not selecting col2. it works fine but i thought i read somewhere it’s not good practice
Swed: Ziddey, I would like to see the source claiming it. I cannot see any problem with the example query
Saraniti: Awesome. i probably misread a long time ago
Levario: I have a table with columns id, userid, courseid, data, and I need to select all entries for each userid, courseid combo, i.e. all entries for user1, course1, user2, course1, user1, course2, user2, course2, etc.
Fallie: I can already filter per courseid, having previously performed a SELECT DISCTINCT courseid
Swed: Ziddey, maybe there was some context in which there was a reason not to do it, but I think that it is quite ok for the general case – as you know exactly what the col2 would contain NULL
Breceda: But how then may I, for a single courseid, select only those entries for a particular userid, without necessary knowing that userid beforehand?
Derrah: That is, I can use the courseid in a WHERE clause, but I want to avoid having to do the same for each user id in each iteration of a single courseid, get me?
Swed: Jmpp, if you do not know it, you can not specify it in the query, so I guess you cannot.
Fogo: So, columns userid, courseid. Say I already know *a* courseid due to a prior selection.
Saven: So I iterate on that loop
Swed: Jmpp, “select userid, courseid from table1 where courseid IN list of ids;” will give you all pairs of courseid,userid for those courses and you can process it in your app
Klemens: Select user,course from . where courseid= . limit 1;
Swed: Jmpp, do you really need to query it in a loop? usually it is better to get all relevant rows in one query and loop over the results
Plotzker: How I thought about doing it in a loop ’cause the table has 5+ million rows, so I wanted to avoid memory problems PHP script
Desnoyers: So I reduce presure by doing it one group at a time
Kramper: But, for that groupid, I want to get *all* entries for a single userid
Kirkman: Give mysql any grouping etc as a single query. it can undoubtly do it better than whatever cod eyou right.