2 Replies Latest reply: Jan 25, 2013 1:26 PM by Arild RSS

    Clarification

    Hillbird
      Hello Alll,

      Oracle Version : 10gR2

      For perfomance iam trying to rewrite the below query but the modified query is giving me wrong results

      Original Query

      SELECT
      MAX(CASE WHEN CRITERIA = 'OLDEST' THEN TO_CHAR(MEM_DATE_OF_BIRTH, 'MM/DD/YYYY') END) AS OLDEST_BIRTH,
      MAX(CASE WHEN CRITERIA = 'OLDEST' THEN MEMBER_NAME END) AS OLDEST_MEMBER,
      MAX(CASE WHEN CRITERIA = 'YOUNGEST' THEN TO_CHAR(MEM_date_of_birth, 'MM/DD/YYYY') END) AS YOUNGEST_BIRTH,
      MAX(CASE WHEN CRITERIA = 'YOUNGEST' THEN MEMBER_NAME END) AS YOUNGEST_MEMBER
      FROM
      (
      SELECT
      'OLDEST' CRITERIA,
      MEM_FIRST_NAME || ' ' ||MEM_LAST_NAME MEMBER_NAME,
      MEM_date_of_birth
      FROM
      MEM_MEMBER
      WHERE MEM_date_of_birth =
      (
      SELECT
      MIN(MEM_date_of_birth)
      FROM
      MEM_MEMBER
      )

      UNION ALL

      SELECT
      'YOUNGEST' CRITERIA,
      MEM_FIRST_NAME || ' ' ||MEM_LAST_NAME MEMBER_NAME,
      MEM_date_of_birth
      FROM
      MEM_MEMBER
      WHERE MEM_date_of_birth =
      (
      SELECT
      MAX(MEM_date_of_birth)
      FROM
      MEM_MEMBER
      )
      ORDER BY
      MEMBER_NAME ASC
      );

      OUTPUT : 01/01/1753 AVONDET 01/17/2013 GIRL TREO SANDERS


      Modified Query
      SELECT distinct
      MIN(TO_CHAR(MEM_date_of_birth, 'MM/DD/YYYY')) over() OLDEST_BIRTH,
      FIRST_VALUE(MEM_FIRST_NAME || ' ' ||MEM_LAST_NAME) OVER(ORDER BY (TO_CHAR(MEM_date_of_birth, 'MM/DD/YYYY'))) OLDEST_MEMBER,
      MAX(TO_CHAR(MEM_DATE_OF_BIRTH, 'MM/DD/YYYY')) OVER() YOUNGEST_BIRTH,
      FIRST_VALUE(MEM_FIRST_NAME || ' ' ||MEM_LAST_NAME) OVER(ORDER BY (TO_CHAR(MEM_date_of_birth, 'MM/DD/YYYY'))desc) YOUNGEST_MEMBER
      --select MIN(TO_CHAR(MEM_date_of_birth, 'MM/DD/YYYY'))OLDEST_BIRTH
      FROM
      MEM_MEMBER;

      OUTPUT : 01/01/1753 AVONDET 12/31/2012 AMAYA JACKSON

      Can someone please guide me out on this. Thanks.
        • 1. Re: Clarification
          Frank Kulash
          Hi,

          Here's one way:
          SELECT DISTINCT
                  TO_CHAR ( MIN (mem_date_of_birth) OVER ()
                    , 'MM/DD/YYYY'
                    )               AS oldest_birth
          ,     FIRST_VALUE (mem_first_name || ' ' || mem_last_name)
                   OVER ( ORDER BY  mem_date_of_birth 
                           ,          primary_key
                     )               AS oldest_member
                  TO_CHAR ( MAX (mem_date_of_birth) OVER ()
                    , 'MM/DD/YYYY'
                    )               AS newest_birth
          ,     FIRST_VALUE (mem_first_name || ' ' || mem_last_name)
                   OVER ( ORDER BY  mem_date_of_birth  DESC
                           ,          primary_key
                     )               AS newest_member
          FROM    mem_member;
          If you'd care to post CREATE TABLE and INSERT statements for your sample data, then I could test this.

          It looks like you had the basic idea, but you were comparing strings, such as '01/17/2013' instead of DATEs, and the string '12/01/1999' comes after the string '01/17/2013;, since '1' comes after '0'. You need to convert the DATEs to strings after the MIN or MAX has been found. You would want to do this even if the strings sorted in the right order. It's more efficient to call TO_CHAR just once, on the MIN or MAX, rather than call it on every row.

          What output would you want if there is a tie for the oldest or newest? The query above picks the contender with the lowest value of primary_key in each case. You can substitute any columns or expressions you want for primary_key.
          • 2. Re: Clarification
            Arild
            The error in your "modified query" seems to be
               MIN (TO_CHAR ( MEM_date_of_birth ....