order by factor in sqldf

Question

As far as I can see, it is not possible to order by a factor in sqldf:

levels( iris$Species )  <- c("virginica", "versicolor", "setosa")
levels(iris$Species)

> sqldf(' select  distinct iris.[Species] from iris order by iris.[Species] ' )
     Species
1     setosa
2 versicolor
3  virginica

The output above shows that sqldf() interprets Species as a character, and orders alphabetically, not by factor.

What is an easy and safe way to work around this?

Note that SQLite does not have a type that corresponds to R's `"factor"` class. it sends such columns as `"character"` to the database so the level information is lost. Upon return it converts them back to factor if feasible (unless you use `method = "raw"` in which case you get back character). — G. Grothendieck, Feb 22 '17 at 13:12

score 3 · Accepted Answer · answered Feb 22 '17 at 12:33

You probably need to put the numeric value of the factor into the data frame and then order on that in your sqldf:

> iris$fnum = as.numeric(iris$Species)
> sqldf(' select iris.[Species] from iris group by iris.[Species] order by iris.fnum')
     Species
1  virginica
2 versicolor
3     setosa

order by factor in sqldf

1 Answers1