8 Replies Latest reply: Jan 16, 2012 4:30 PM by Syed Ullah RSS

    Oracle Select Max Date

    597494
      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
          How about this?

          select * from eg a where a.datecol = (select max(b.datecol) from eg b)
          • 2. Re: Oracle Select Max Date
            marias
            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
              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
                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
                  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
                    Rob van Wijk
                    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
                      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
                        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;