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.