CODEWITHSUNDEEP
mysqlsqlrsqlitedplyr
Stéphane Laurent
Stéphane Laurent

Reputation: 84529

How to extract a subset of a tbl_mysql or tbl_sqlite data frame by row numbers?

As I understand the help page of the slice function of the dplyr package, one can select the rows of an SQL tbl or an SQLite tbl like this:

filter(mtcars, row_number() == 1L)

However I get an error when I try:

dat1 <- data.frame(
  Name = c("Joe", "Bill", "Jim", "Kate"),
  Value = c(10.1, 9, 7.7, -3),
  Indic = c(0L, 1L, 2L, 2L),
  Status = c(TRUE, TRUE, FALSE, FALSE)
)
#### Create SQLite database ####
library(dplyr)
my_database <- src_sqlite("db_sqlite_file", create = TRUE) 
#### Put data in database ####
copy_to(my_database, dat1, temporary = FALSE)
#### Connect to database ####
my_db <- src_sqlite("db_sqlite_file", create = FALSE)
#### Querying the database ####
dd <- tbl(my_db, "dat1")
ddextract <- filter(dd, row_number() == 1L)

The error message occurs here:

> ddextract  
Source: sqlite 3.8.6 [db_sqlite_file]
Error in sqliteSendQuery(con, statement, bind.data) : 
  error in statement: no such function: ROW_NUMBER

The same problem with MySQL:

my_db_sql <- src_mysql("mysql_dplyr", password="mypassword") 
dd <- tbl(my_db_sql, "dat1")
ddextract <- filter(dd, row_number() == 1L)

The error:

> ddextract
Source: mysql 5.5.49-0ubuntu0.14.04.1 [root@localhost:/mysql_dplyr]
Error in .local(conn, statement, ...) : 
  could not run statement: FUNCTION mysql_dplyr.ROW_NUMBER does not exist

Of course I could create a new column with the row numbers, but I'm wondering why filter(mtcars, row_number() == 1L) does not work.

Upvotes: 1

Views: 391

Answers (1)

Vincent Guyader
Vincent Guyader

Reputation: 3189

You can try this:

ddextract <- dd %>% collect() %>% filter(row_number() == 1L)

Upvotes: 1

Related Questions