1

i got this error while i have to transpose row into column

library(sqldf)
sqldf("SELECT id,name,ROW_NUMBER() over (PARTITION BY id order by id) as row_no FROM dd")

Error in rsqlite_send_query(conn@ptr, statement) : near “(”: syntax error_____

what should i do or please give me an alternative as i want to use it in a shiny app

moodymudskipper
  • 46,417
  • 11
  • 121
  • 167
rushi bagul
  • 31
  • 2
  • Is this a duplicate of https://stackoverflow.com/questions/43196180/error-in-rsqlite-send-query ? – IRTFM Apr 04 '18 at 23:33
  • Possible duplicate of [sqlite equivalent of row\_number() over ( partition by ...?](https://stackoverflow.com/questions/4074257/sqlite-equivalent-of-row-number-over-partition-by) – moodymudskipper Apr 04 '18 at 23:36

2 Answers2

1

Assume we have this:

library(sqldf)
dd <- data.frame(id = c(1, 2, 3), name = c("A", "B", "C"), X = 1:9)

Then these three all give the same result:

# 1. PostgreSQL - supports over/partition

library(RPostgreSQL)
sqldf("SELECT id, name, \"X\", ROW_NUMBER() over (PARTITION BY id order by id) as row_no 
       FROM dd order by id")

# 2. SQLite - does not support over/partition, use join instead

# Ensure RPostgreSQL is NOT loaded to force use of SQLite.
sqldf("select a.*, count(*) row_no 
       from dd a join dd b on a.id = b.id and b.rowid <= a.rowid
       group by a.rowid
       order by a.rowid")

# 3. Base R

transform(dd, row_no = ave(1:nrow(dd), id, FUN = seq_along))
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
0

sqlite doesn't support partitions.

You may want to check this link

moodymudskipper
  • 46,417
  • 11
  • 121
  • 167