This discussion is archived
8 Replies Latest reply: Jan 16, 2012 2:30 PM by Syed Ullah RSS

Oracle Select Max Date

597494 Newbie
Currently Being Moderated
Hi,

I need help with a Select statement that returns the most recent revision date from an Oracle database.

This is my Select statement:

SELECT
STORE.ITEM.ID,
STORE.ITEM.ITEM_NUMBER,
STORE.ITEM.CATEGORY,
STORE.ITEM.DESCRIPTION,
STORE.REV.REV_NUMBER,
STORE.REV.OBSOLETE_DATE,
STORE.REV.RELEASE_DATE

FROM
STORE.ITEM,
STORE.REV

WHERE
STORE.ITEM.ID = STORE.REV.ITEM AND
(STORE.REV.OBSOLETE_DATE IS NULL) AND
(STORE.ITEM.ITEM_NUMBER = '100.009')

ORDER BY
STORE.REV.RELEASE_DATE DESC

The result is:

4295110 100.009 207531 Widget001 07 NULL 10/20/2006 11:33:33 AM <-------------- Would like to have only this one item
4295110 100.009 207531 Widget001 06 NULL 9/20/2005 7:57:26 AM
4295110 100.009 207531 Widget001 05 NULL 11/13/2003 9:17:35 AM
4295110 100.009 207531 Widget001 04 NULL 11/19/2001 12:10:25 PM
4295110 100.009 207531 Widget001 03 NULL 3/21/2001 1:01:07 PM
4295110 100.009 207531 Widget001 2 NULL 2/6/1999 10:55:19 AM


Could someone please help me get the item with the most recent STORE.REV.RELEASE_DATE.



Thanks.
  • 1. Re: Oracle Select Max Date
    504410 Newbie
    Currently Being Moderated
    How about this?

    select * from eg a where a.datecol = (select max(b.datecol) from eg b)
  • 2. Re: Oracle Select Max Date
    marias Explorer
    Currently Being Moderated
    or you can try like this...
    select * from test_emp 
    where trunc(hire_date) in (select max(trunc(hiredate)) from test_emp group by deptno);
    ***un-tested***
  • 3. Re: Oracle Select Max Date
    597494 Newbie
    Currently Being Moderated
    Hi user501407,

    I tried to modify your codes to fit mine and it did not work. Any more suggeations?
  • 4. Re: Oracle Select Max Date
    597494 Newbie
    Currently Being Moderated
    Hi Marias,

    You post sort of worked. The problem is that the group by groups similar items. The revision is always different hence it always displays the full results.

    Is there a way to use MAX without the group by clause?
  • 5. Re: Oracle Select Max Date
    Sentinel Pro
    Currently Being Moderated
    Try this:
    SELECT STORE.ITEM.ID,
      STORE.ITEM.ITEM_NUMBER,
      STORE.ITEM.CATEGORY,
      STORE.ITEM.DESCRIPTION,
      STORE.REV.REV_NUMBER,
      STORE.REV.OBSOLETE_DATE,
      STORE.REV.RELEASE_DATE
    
    FROM
      (SELECT STORE.ITEM.ID,
         STORE.ITEM.ITEM_NUMBER,
         STORE.ITEM.CATEGORY,
         STORE.ITEM.DESCRIPTION,
         STORE.REV.REV_NUMBER,
         STORE.REV.OBSOLETE_DATE,
         STORE.REV.RELEASE_DATE,
         ROW_NUMBER() OVER(PARTITION BY STORE.ITEM.ID
       ORDER BY STORE.REV.RELEASE_DATE DESC) RN
    
       FROM STORE.ITEM,
         STORE.REV
    
       WHERE STORE.ITEM.ID = STORE.REV.ITEM
       AND(STORE.REV.OBSOLETE_DATE IS NULL))
    
    WHERE RN = 1
     AND(STORE.ITEM.ITEM_NUMBER = '100.009');
    Note the above code is untested.
  • 6. Re: Oracle Select Max Date
    RobvanWijk Oracle ACE
    Currently Being Moderated
    SQL> create table item (id,item_number,category,description)
      2  as
      3  select 4295110, '100.009', 207531, 'widget001' from dual
      4  /

    Tabel is aangemaakt.

    SQL> create table rev (item,rev_number,obsolete_date,release_date)
      2  as
      3  select 4295110, '07', null, to_date('10/20/2006 11:33:33 AM','mm/dd/yyyy hh:mi:ss am') from dual union all
      4  select 4295110, '06', null, to_date('09/20/2005 7:57:26 AM','mm/dd/yyyy hh:mi:ss am') from dual union all
      5  select 4295110, '05', null, to_date('11/13/2003 9:17:35 AM','mm/dd/yyyy hh:mi:ss am') from dual union all
      6  select 4295110, '04', null, to_date('11/19/2001 12:10:25 PM','mm/dd/yyyy hh:mi:ss am') from dual union all
      7  select 4295110, '03', null, to_date('3/21/2001 1:01:07 PM','mm/dd/yyyy hh:mi:ss am') from dual union all
      8  select 4295110, '2', null, to_date('2/6/1999 10:55:19 AM','mm/dd/yyyy hh:mi:ss am') from dual union all
      9  select 4295110, '1', sysdate, to_date('2/6/1998 10:55:19 AM','mm/dd/yyyy hh:mi:ss am') from dual
    10  /

    Tabel is aangemaakt.

    SQL> select item.id
      2       , item.item_number
      3       , item.category
      4       , item.description
      5       , max(rev_number) keep (dense_rank last order by release_date)
      6       , max(obsolete_date) keep (dense_rank last order by release_date)
      7       , max(release_date)
      8    from item
      9       , rev
    10   where item.id = rev.item
    11     and rev.obsolete_date is null
    12     and item.item_number = '100.009'
    13   group by item.id
    14       , item.item_number
    15       , item.category
    16       , item.description
    17  /

            ID ITEM_NU   CATEGORY DESCRIPTI MA MAX(OBSOLETE_DATE)K MAX(RELEASE_DATE)
    ---------- ------- ---------- --------- -- ------------------- -------------------
       4295110 100.009     207531 widget001 07                     20-10-2006 11:33:33

    1 rij is geselecteerd.
    Regards,
    Rob.
  • 7. Re: Oracle Select Max Date
    Lynn L Newbie
    Currently Being Moderated
    Here's a more general example of finding a account information from Oracle Applications TCA tables where the latest transaction date exceeds a date certain:

    SELECT CA.cust_account_id, CA.account_number, CA.orig_system_reference Account_OSR,
    CA.account_name, CA.subcategory_code Type, CA.tax_code,
    P.party_id, P.party_number, P.party_name,
    REL.object_id "ParentPartyID", PP.party_number "ParentPartyNum", PP.party_name "ParentName",
    P.address1, P.address2, P.address3,
    P.city, P.state, P.postal_code, P.country,
    P.email_address,
    P.primary_phone_area_code area_code, P.primary_phone_number phone_num
    FROM HZ_CUST_ACCOUNTS CA,
    HZ_PARTIES P,
    HZ_PARTIES PP,
    HZ_RELATIONSHIPS REL
    WHERE CA.party_id = P.party_id
    AND P.party_id = REL.subject_id (+)
    AND PP.party_id = REL.object_id
    AND REL.subject_table_name = 'HZ_PARTIES'
    AND REL.relationship_code = 'SUBSIDIARY_OF'
    AND REL.status = 'A'
    AND CA.cust_account_id IN (SELECT sold_to_org_id
    FROM (SELECT MAX(OOHA.ordered_date), sold_to_org_id
    FROM ONT.OE_ORDER_HEADERS_ALL OOHA
    GROUP BY OOHA.sold_to_org_id
    HAVING MAX(OOHA.ordered_date) > TO_DATE('12/31/2004', 'MM/DD/YYYY')))
    ORDER BY CA.account_name;
    /

    Simplifying back to your problem, I deduced the following should be close (untested):

    SELECT I.ID,
    I.ITEM_NUMBER,
    I.CATEGORY,
    I.DESCRIPTION,
    R.REV_NUMBER,
    R.OBSOLETE_DATE,
    R.RELEASE_DATE
    FROM STORE.ITEM I
    WHERE R.OBSOLETE_DATE IS NULL
    AND I.ITEM_NUMBER = '100.009'
    AND I.id IN (SELECT item
    FROM (SELECT MAX(R.rev_number), R.item
    FROM STORE.REV R
    GROUP BY R.item));

    So, the correlated sub-query retrieves the maximum rev level for each item (or I.ID). Hopefully, this will operate only on the one item, specified in the outer query.

    I strongly recommend using column and table aliases to improve readability.

    HTH
  • 8. Re: Oracle Select Max Date
    Syed Ullah Journeyer
    Currently Being Moderated
    select *
    from (
        SELECT
        STORE.ITEM.ID,
        STORE.ITEM.ITEM_NUMBER,
        STORE.ITEM.CATEGORY,
        STORE.ITEM.DESCRIPTION,
        STORE.REV.REV_NUMBER,
        STORE.REV.OBSOLETE_DATE,
        STORE.REV.RELEASE_DATE
        
        FROM
        STORE.ITEM,
        STORE.REV
        
        WHERE
        STORE.ITEM.ID = STORE.REV.ITEM AND
        (STORE.REV.OBSOLETE_DATE IS NULL) AND
        (STORE.ITEM.ITEM_NUMBER = '100.009')
        
        ORDER BY
        STORE.REV.RELEASE_DATE DESC
    )
    where rownum = 1;