# Package sqldf eases the multivariable sorting pain

This will be a quick one.  I was trying to sort my dataframe so that it went in ascending order on one variable and descending order on another variable.  This was really REALLY bothersome to try to figure out with base R functions.  Then I remembered sqldf!

```# Assuming dataframe named 'mydf' and 'V1' and 'V2' are your variables you want to sort on in opposite directions
library(sqldf)
mydf.sorted = sqldf("SELECT * FROM mydf ORDER BY V1 ASC, V2 DESC")```

That’s all! No pain, and lots of gain. Show me how to do this so simply with base R and I’ll be impressed.

EDIT:

After many comments on this post, it’s become painfully obvious to me that I wasn’t using the right search terms (or looking at that negative sign on the second argument to the order function) when searching for a base R solution to this problem. Well, we all make mistakes, right? I hope this post has still been helpful to others, and it isn’t a completely redundant internet resource on this topic!

## 18 thoughts on “Package sqldf eases the multivariable sorting pain”

1. If V1 and V2 are numerical variables, you could multiply one (let’s say V2) with -1 and then sort both of them ascending. If you multiply after the sort V2 by -1, you will have sorted by V1 ascending and V2 descending. This is untested, and I have no idea how you could do the same with character vectors (possibly convert to factors, you might be able to do similar?).
But using sqldf is much easier to read.

2. I like doBy::orderBy()

library(doBy)
orderBy(~ +V1 -V2, data = mydf)

• Thank you! This is awesomely concise syntax! Does it work with character and numeric data alike?

• Yes. These two should give the same results.

V1 V2
a -1
z 1
a 3
d -3
f 1
c 7
f 3
d 5
")

mydf

library(sqldf)
sqldf("SELECT * FROM mydf ORDER BY V1 ASC, V2 DESC")

library(doBy)
orderBy(~ +V1 -V2, data = mydf)

3. “order”.

LIke this:

attach(mydf)
ord=order(V1,-V2)
detach(mydf)
mydf.sorted=mydf[ord,]

• First commandment of R : thou shall not use attach (never)

4. mydf[with(mydf, order(V1,-V2)), ]

• But, as you’ve shown, SQL syntax is a much easier to read.

5. I use data.table library. So it is mydt <- data.table(mydf) and mydt[order(V1, -V2)].

6. Just some sugar around Ken’s solution, seems even more elegant than sqldf way:
mydf.sorted <- with(mydf, mydf[order(V1, -V2),])

• mydf.sorted <- with(mydf, mydf[order(V1, -xtfrm(V2),])
This will always work even with characters and factors

7. sqldf is great and very fast! I use it especially for aggregations using GROUP BY. Even data frames / tables consisting of more than a hundred thousand rows are often done within a second.

8. mydf[with( mydf, order( V1, -V2 ) , ] # data.frame way
or
mydf[order( V1, -V2 )] # data.table way

9. Standard R way is to use combination of order and xtfrm. Something like: iris[with(iris, order(-xtfrm(Species),Sepal.Length)),] This works even for character columns. ﻿See ?order