max function in R not giving desired Date results

Question

I am trying to use Max function to get the record with latest dates but it does not give desired results as it also gives data with old dates.Below is output of dataframe OA_Output:

 Org_ID   ORG_OFFICIAL_NORM_NAME  ORG_IMMEDIATE_PARENT_SRC   ORG_IP_SRC_MD_Date 
--------  ----------------------  ------------------------   ------------------ 
 132693   BOLLE INCORPORATED          abc.com               26-JUN-18
 122789   BEE STINGER, LLC            aa.com                12-Mar-18
 344567   CALIBER COMPANY             xyz.com               16-Feb-16
 639876   Maruti                      yy.com                23-Jun-17

I am running below R Code to get the records with latest dates:

gautam1 <-

  sqldf(

    "
    SELECT ORG_OFFICIAL_NORM_NAME,ORG_IMMEDIATE_PARENT_SRC
   ,MAX(ORG_IP_SRC_MD_DATE),ROW_ID
    FROM OA_output
    where ROW_ID = 1
    and ORG_IMMEDIATE_PARENT_SRC like '%exhibit21%'
    GROUP BY ORG_IMMEDIATE_PARENT_ORGID
    ORDER BY ORG_IMMEDIATE_PARENT_ORGID

    "    )

In The above code max function is not giving desired results.I am not sure whether there is a date format issue between Oracle and R. Any help will be appreciated Thanks Gautam

Does your `Date` column has correct type? You can use `class()`? — franiis, Aug 06 '18 at 10:52
yes it is of Date type. how should i use Class (). can you help me — Gautam Biswas, Aug 06 '18 at 11:01
this is an SQL question rather than an R question. The max function is used in SQL only. — Cettt, Aug 06 '18 at 11:24
Please make your input dataframe [`OA_output` reproducible](http://stackoverflow.com/questions/5963269). — zx8754, Aug 06 '18 at 12:02
sqldf does not work with Oracle. It works with SQLite, H2, MySQL and PostgreSQL. — G. Grothendieck, Aug 06 '18 at 12:06
you need to show the result of `dput(output)` to make the question reproducible or if it is large cut down `output` to a few rows but enough to illustrate and then dput that. — G. Grothendieck, Aug 06 '18 at 13:16
I have provided the output from the dataframe OA_Output. I showed few records as the output is too huge to show here. — Gautam Biswas, Aug 07 '18 at 07:01

score 0 · Answer 1 · answered Aug 07 '18 at 07:08

0

can you please use below query as a sql and run again code

  SELECT ORG_OFFICIAL_NORM_NAME,ORG_IMMEDIATE_PARENT_SRC
       ,MAX(ORG_IP_SRC_MD_DATE),ROW_ID
        FROM OA_output
        where ROW_ID = 1
        and ORG_IMMEDIATE_PARENT_SRC like '%exhibit21%'
        GROUP BY ORG_OFFICIAL_NORM_NAME,ORG_IMMEDIATE_PARENT_SRC,ROW_ID
        ORDER BY ORG_IMMEDIATE_PARENT_ORGID

answered Aug 07 '18 at 07:08

Zaynul Abadin Tuhin

31,407
5
33
63

I need to run the above query in R to get the records with latest dates – Gautam Biswas Aug 07 '18 at 07:17
@GautamBiswas does this query return your expected result in db editor? if it is return your expected result in db editor then definitely it will return in R, so my idea 1st run this sql code in db then check is that correct or not? then move new strategy – Zaynul Abadin Tuhin Aug 07 '18 at 07:23

max function in R not giving desired Date results

1 Answers1