[R] Merging Data.Tables on conditions other than equality

[Brian Trautman]

I have two tables that I would like to join together in a way equivalent to
the following SQL.  Note that I'm using a "greater than" statement in my
join, rather than checking for equality.

require(sqldf)
require(data.table)


dt <- data.table(num=c(1, 2, 3, 4, 5, 6), char=c('A', 'A', 'A', 'B', 'B',
'B'))

dt_out_sql <- sqldf('
select dtone.num as num1, dttwo.num as num2, dttwo.char
from dt as dtone INNER join dt as dttwo on
(dtone.char = dttwo.char) and
(dtone.num *>=* dttwo.num)
')

I realize that I can use the below code, but would like to do the merging
and filtering in the same step (my data sets are large enough for
performance/memory concerns to come into play.

dt_out_r <- merge(x=dt, y=dt, by = c('char'), allow.cartesian=TRUE)
dt_out_r <- dt_out_r[num.x >= num.y]

Thank you very much!

	[[alternative HTML version deleted]]