[R] Question regarding sqldf

[Gabor Grothendieck]

I just answered essentially the same question from another person offline
within the last day.  Anyways:

"Date" class variables are stored as numbers, i.e. days since
the Epoch, in R and when transferred to sqlite get transferred
as numbers.  Try this:

> library(sqldf)
> DF <- data.frame(a = Sys.Date() + 1:5, b = 1:5)
> DF
          a b
1 2009-07-31 1
2 2009-08-01 2
3 2009-08-02 3
4 2009-08-03 4
5 2009-08-04 5
> Sys.Date() + 2
[1] "2009-08-01"
> s <- sprintf("select * from DF where a >= %d", Sys.Date() + 2)
> s
[1] "select * from DF where a >= 14457"
> sqldf(s)
          a b
1 2009-08-01 2
2 2009-08-02 3
3 2009-08-03 4
4 2009-08-04 5

> # to compare against character string store a as character
> DF2 <- transform(DF, a = as.character(a))
> sqldf("select * from DF2 where a >= '2009-08-01'")
          a b
1 2009-08-01 2
2 2009-08-02 3
3 2009-08-03 4
4 2009-08-04 5

On Thu, Jul 30, 2009 at 1:46 PM, <Rizzo.Michael at epamail.epa.gov> wrote:
>
> Hello,
>
> I am having a problem using sqldf.  I'm trying to choose a subset of
> observations from a data set based on the date and maximum value of a
> variable by date.
>
> Here is the code I am using:
>
> test<-sqldf("select distinct * from bextuse group by sdate having
> bext=max(bext)",method="raw");
>
> The result I get back is a data frame with 0 rows and 0 columns.  I have
> tried the code in another program that utilizes SQL, and I retrieve the
> 14 rows I was expecting to get.
> I looked at the SQLite information on the web, and it mentions that the
> "having" clause can be used to select observations from grouped data
> using aggregating functions.
>
> Any help or advice is greatly appreciated.
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>