Syntax error in SQL code being used in R, no row_number function found

I am using RSQLite, DBI, and dbplyr packages. I’m watching this YouTube video by TechTFQ.

library(dbplyr)
library(RSQLite)
library(DBI)

mtcars <- tibble::as_tibble(mtcars, rownames = 'car')  
sql_mtcars <- mtcars

con <- RSQLite::dbConnect(SQLite(), ":memory:")
dplyr::copy_to(con, sql_mtcars)

sql_mtcars_db <- tbl(con, "sql_mtcars")

sql_mtcars_db %>%
  dplyr::select(car, mpg, wt) %>%
  dplyr::show_query()

dbListTables(con)

I’m getting a syntax error with this line.

DBI::dbGetQuery(con, '
SELECT *
  row_number() over(partition by cyl) AS rn
FROM sql_mtcars 
  ')

Error: near “row_number”: syntax error

I’m certain that the SQL syntax I’m writing here is the same as what I’m watching in the YouTube video, except for the differences in the dataframe that I’m using and the variables that I’m calling.

What is the error that I’m running into? Is it the case that the dbplyr SQL doesn’t have the full range of SQL verbs, or is it using a different SQL syntax?

I’m not even sure how to find a a key for the SQL syntax to use in this method. Asking for help through ?DBI, ?dbplyr or ?RSQLite is not much help because there doesn’t seem to be much in the way of documentation about the SQL verbs, and I haven’t found a very comprehensive vignette to study.

Answer

It looks like you are missing a comma after the *. The following works as expected:

DBI::dbGetQuery(con, '
SELECT *,
  row_number() over(partition by cyl) AS rn
FROM sql_mtcars 
  ')

Note the addition of a comma after the * and before the row_number.

Advertisement

Answer