StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>One option is to manipulate the SQL string within the loop. At the moment you have a string literal, the <code>'df[2]'</code> is not interpreted by R as anything other than characters. There are going to be some ambiguities in my answer, because <code>df</code> in your Q is patently not a data frame (it is a character vector!). Something like this will do what you want.</p> <p>Store the output in a numeric vector:</p> <pre><code>require(RMySQL) df <- c('a','b','c') out <- numeric(length(df)) names(out) <- df </code></pre> <p>Now we can loop over the elements of <code>df</code> to execute your query three times. We can set the loop up two ways: i) with <code>i</code> as a number which we use to reference the elements of <code>df</code> and <code>out</code>, or ii) with <code>i</code> as each element of <code>df</code> in turn (i.e. <code>a</code>, then <code>b</code>, ...). I will show both versions below.</p> <pre><code>## Version i for(i in seq_along(df)) { SQL <- paste("SELECT max(ID) FROM table WHERE columna='", df[i], "';", sep = "") out[i] <- dbGetQuery(con, SQL) dbDisconnect(con) } </code></pre> <p>OR:</p> <pre><code>## Version ii for(i in df) { SQL <- paste("SELECT max(ID) FROM table WHERE columna='", i, "';", sep = "") out[i] <- dbGetQuery(con, SQL) dbDisconnect(con) } </code></pre> <p>Which you use will depend on personal taste. The second (ii) version requires you to set names on the output vector <code>out</code> that are the same as the data inside <code>out</code>.</p> <p>Having said all that, assuming your actual SQL Query is similar to the one you post, can't you do this in a single SQL statement, using the <code>GROUP BY</code> clause, to group the data before computing <code>max(ID)</code>? Doing simple things in the data base like this will likely be much quicker. Unfortunately, I don't have a MySQL instance around to play with and my SQL-fu is weak currently, so I can't given an example of this.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload