CODEWITHSUNDEEP
rsqlitedplyrrsqlitedbplyr
Dhiraj
Dhiraj

Reputation: 1720

Collect() from table in SQLite in R

I am trying to use the collect() function from dplyr to query a table (comp_fleet) in SQLite. The following code works:

Cposns <- tbl(db_con,"comp_fleet") %>% 
  arrange(mmsi, desc(timestamp))%>% 
  dplyr::filter(!is.na(lat)) %>% collect()

However when I try top_n(), I get an error:

Cposns <- tbl(db_con,"comp_fleet") %>% 
  arrange(mmsi, desc(timestamp))%>% 
  dplyr::filter(!is.na(lat)) %>% 
  top_n(1,timestamp) %>% collect()

Error: Window function `rank()` is not supported by this database

Since this is a huge table, I was hoping to carry out all aggregation functions within the database before collecting. Is there some way of using the top_n() function prior to collect()? Or something equivalent to it?

Upvotes: 1

Views: 657

Answers (1)

Dhiraj
Dhiraj

Reputation: 1720

Thanks to @Scarabee, this is what works:

Cposns <- tbl(db_con,"comp_fleet") %>%
  arrange(mmsi, desc(timestamp))%>%
  dplyr::filter(!is.na(lat)) %>% group_by(mmsi) %>%
  do(head(., n = 1)) %>% collect()

Upvotes: 1

Related Questions