locked
how to retrieve max date time record for each ID (oracle) RRS feed

  • Question

  • User-583959464 posted

    i have two column, 1st column store the "ID and date" format (IDXXX-YYYYMMDD) where IDXXX is the record ID and 2nd column store the time. how can i retrieve the record which is latest date and latest time for each ID in sql in oracle?

    ID_DATE                           TIME 

    ID001-20170930              1750

    ID001-20170930               1830

    ID001-20171001               1030

    ID001-20171002               1230

    ID002-20171101               1000

    my expected result as below

    ID001-2017002                 1230

    ID002-20171101                1000

    Thursday, October 19, 2017 2:08 PM

All replies

  • User753101303 posted

    Hi,

    Not familiar with Oracle but my understanding is that you want something such as :

    SELECT MAX(ID_DATE+TIME) FROM Table GROUP BY LEFT(ID_DATE,5) which should show :

    ID001-20170021230
    ID002-201711011000

    the problem being that your first column store both an id and a date while date and time that should work together are splitted.

     

    Thursday, October 19, 2017 5:46 PM