R help - May 2012 - plz help. how to filter/group/sort data on mass data

hi, nice people
          I needs to compute the product whose annual sales values are all
among the top 100 using R ( version 2.15.0)
          data is stored in the MSSQL database.
          data structure( sales table's fields): productID, time, value
         I have found the SQL solution below:
SQL solution is as below:
--------------------------------------------------------------
WITH sales1 AS (
 SELECT productID, YEAR(time) AS year, SUM(value) AS value1
 FROM sales
 GROUP BY productID, YEAR(time)
)

SELECT productID
FROM (
 SELECT productID
 FROM (
 SELECT productID,RANK() OVER(PARTITION BY year ORDER BY value1 DESC)
rankorder 
FROM sales1 ) T1
 WHERE rankorder<=100) T2
GROUP BY productID
HAVING COUNT(*)=(SELECT COUNT(DISTINCT year ) FROM sales1)
--------------------------------------------------------------------------
now, I must solve it using R ( I need stepwise analysis capability in
further ).
I have retrive data from database as below:
----------------------------------------------------------------
 library(RODBC)
 odbcDataSources()
 conn=odbcConnect("sqlsvr")
 result=sqlQuery(conn,'select * from sales')
 odbcClose(conn)
 result
--------------------------------------------------------------
But I dont' know how to process next step, such as filter,sort,group
please give me some help.


--
View this message in context:
http://r.789695.n4.nabble.com/plz-help-how-to-filter-group-sort-data-on-mass-data-tp4630714.html
Sent from the R help mailing list archive at Nabble.com.

any suggestion? plz help me.

--
View this message in context:
http://r.789695.n4.nabble.com/plz-help-how-to-filter-group-sort-data-on-mass-data-tp4630714p4630820.html
Sent from the R help mailing list archive at Nabble.com.

I have know how to sort and filter and group.
can anyone answer my another question?
Is there any function in R like *lead *and *lag * in SQL. They are relative
position function. We can use them to solve problem such as : on
year-on-year basis, link relative ratio

can anyone give a tips?

--
View this message in context:
http://r.789695.n4.nabble.com/plz-help-how-to-filter-group-sort-data-on-mass-data-tp4630714p4631653.html
Sent from the R help mailing list archive at Nabble.com.

type

??lag

at the R command line.
---------------------------------------------------------------------------
Jeff Newmiller                        The     .....       .....  Go Live...
DCN:<jdnewmil at dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live
Go...
                                      Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
/Software/Embedded Controllers)               .OO#.       .OO#.  rocks...1k
--------------------------------------------------------------------------- 
Sent from my phone. Please excuse my brevity.

bestbird <bestbird7788 at gmail.com> wrote:
> I have know how to sort and filter and group.
>can anyone answer my another question?
>Is there any function in R like *lead *and *lag * in SQL. They are
>relative
>position function. We can use them to solve problem such as : on
>year-on-year basis, link relative ratio
>
>can anyone give a tips?
>
>--
>View this message in context:
>http://r.789695.n4.nabble.com/plz-help-how-to-filter-group-sort-data-on-mass-data-tp4630714p4631653.html
>Sent from the R help mailing list archive at Nabble.com.
>
>______________________________________________
>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.

thank you.
diff   can do this

--
View this message in context:
http://r.789695.n4.nabble.com/plz-help-how-to-filter-group-sort-data-on-mass-data-tp4630714p4632494.html
Sent from the R help mailing list archive at Nabble.com.