OraFAQ Forum: SQL & PL/SQL » Best solution to retrieve result

Home » SQL & PL/SQL » SQL & PL/SQL » Best solution to retrieve result (Oracle 11.2 Windows)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Best solution to retrieve result [message #569902]

Fri, 02 November 2012 20:18

Duane
Messages: 353
Registered: December 2002

Senior Member

Just wondering if anyone knows the best way to get the result I need.

Result:
ID: 111111
STDNT_CAR_NBR: 1
ACAD_PLAN: BIOLB
EFFDT: 05/6/2012
EFFSEQ: 3
PLAN_SEQUENCE: 1

Rules: Lowest student career number (STDNT_CAR_NBR) that has the lowest plan sequence (PLAN_SEQUENCE) for that stdnt career number (STDNT_CAR_NBR), all of which is based upon the effective dating (EFFDT) with the highest EFFSEQ.

Test Data:


CREATE TABLE STU_PLAN
(
  ID             VARCHAR2(11 BYTE),
  STDNT_CAR_NBR  NUMBER,
  EFFDT          DATE,
  EFFSEQ         NUMBER,
  ACAD_PLAN      VARCHAR2(10 BYTE),
  PLAN_SEQUENCE  NUMBER
)

Insert into STU_PLAN
   (ID, STDNT_CAR_NBR, EFFDT, EFFSEQ, ACAD_PLAN, 
    PLAN_SEQUENCE)
 Values
   ('111111', 0, TO_DATE('08/21/2000 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 0, 'INF-TC', 
    10);
Insert into STU_PLAN
   (ID, STDNT_CAR_NBR, EFFDT, EFFSEQ, ACAD_PLAN, 
    PLAN_SEQUENCE)
 Values
   ('111111', 0, TO_DATE('12/16/2000 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 0, 'INF-TC', 
    10);
Insert into STU_PLAN
   (ID, STDNT_CAR_NBR, EFFDT, EFFSEQ, ACAD_PLAN, 
    PLAN_SEQUENCE)
 Values
   ('111111', 0, TO_DATE('06/04/2001 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 0, 'INF-TC', 
    10);
Insert into STU_PLAN
   (ID, STDNT_CAR_NBR, EFFDT, EFFSEQ, ACAD_PLAN, 
    PLAN_SEQUENCE)
 Values
   ('111111', 0, TO_DATE('08/15/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 0, 'LIB-ART', 
    10);
Insert into STU_PLAN
   (ID, STDNT_CAR_NBR, EFFDT, EFFSEQ, ACAD_PLAN, 
    PLAN_SEQUENCE)
 Values
   ('111111', 0, TO_DATE('08/15/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 'LIB-ART', 
    10);
Insert into STU_PLAN
   (ID, STDNT_CAR_NBR, EFFDT, EFFSEQ, ACAD_PLAN, 
    PLAN_SEQUENCE)
 Values
   ('111111', 0, TO_DATE('12/19/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 0, 'LIB-ART', 
    10);
Insert into STU_PLAN
   (ID, STDNT_CAR_NBR, EFFDT, EFFSEQ, ACAD_PLAN, 
    PLAN_SEQUENCE)
 Values
   ('111111', 1, TO_DATE('01/01/2004 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 0, 'LIB-ART', 
    10);
Insert into STU_PLAN
   (ID, STDNT_CAR_NBR, EFFDT, EFFSEQ, ACAD_PLAN, 
    PLAN_SEQUENCE)
 Values
   ('111111', 1, TO_DATE('05/16/2004 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 0, 'LIB-ART', 
    10);
Insert into STU_PLAN
   (ID, STDNT_CAR_NBR, EFFDT, EFFSEQ, ACAD_PLAN, 
    PLAN_SEQUENCE)
 Values
   ('111111', 1, TO_DATE('08/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 'BIOLB', 
    1);
Insert into STU_PLAN
   (ID, STDNT_CAR_NBR, EFFDT, EFFSEQ, ACAD_PLAN, 
    PLAN_SEQUENCE)
 Values
   ('111111', 1, TO_DATE('08/16/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 'BIOLB', 
    1);
Insert into STU_PLAN
   (ID, STDNT_CAR_NBR, EFFDT, EFFSEQ, ACAD_PLAN, 
    PLAN_SEQUENCE)
 Values
   ('111111', 1, TO_DATE('08/16/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2, 'BIOLB', 
    1);
Insert into STU_PLAN
   (ID, STDNT_CAR_NBR, EFFDT, EFFSEQ, ACAD_PLAN, 
    PLAN_SEQUENCE)
 Values
   ('111111', 1, TO_DATE('08/16/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 'BIOLB', 
    1);
Insert into STU_PLAN
   (ID, STDNT_CAR_NBR, EFFDT, EFFSEQ, ACAD_PLAN, 
    PLAN_SEQUENCE)
 Values
   ('111111', 1, TO_DATE('01/08/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 0, 'BIOLB', 
    1);
Insert into STU_PLAN
   (ID, STDNT_CAR_NBR, EFFDT, EFFSEQ, ACAD_PLAN, 
    PLAN_SEQUENCE)
 Values
   ('111111', 1, TO_DATE('05/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 'BIOLB', 
    1);
Insert into STU_PLAN
   (ID, STDNT_CAR_NBR, EFFDT, EFFSEQ, ACAD_PLAN, 
    PLAN_SEQUENCE)
 Values
   ('111111', 1, TO_DATE('05/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2, 'BIOLB', 
    1);
Insert into STU_PLAN
   (ID, STDNT_CAR_NBR, EFFDT, EFFSEQ, ACAD_PLAN, 
    PLAN_SEQUENCE)
 Values
   ('111111', 1, TO_DATE('05/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 'BIOLB', 
    1);
COMMIT;

[Updated on: Sat, 03 November 2012 01:08] by Moderator

Report message to a moderator

Re: Best solution to retrieve result [message #569903 is a reply to message #569902]

Fri, 02 November 2012 20:51

BlackSwan
Messages: 20086
Registered: January 2009

Senior Member


  1* select * from stu_plan where effseq = ( select max(effseq) from stu_plan)
SQL> /

ID          STDNT_CAR_NBR EFFDT                   EFFSEQ ACAD_PLAN  PLAN_SEQUENCE
----------- ------------- ------------------- ---------- ---------- -------------
111111                  1 2011-08-16 00:00:00          3 BIOLB                  1
111111                  1 2012-05-06 00:00:00          3 BIOLB                  1

why should record containing "2012-05-06" be included & not the record containing "2011-08-16"?

Report message to a moderator

Re: Best solution to retrieve result [message #569904 is a reply to message #569903]

Fri, 02 November 2012 20:55

Duane
Messages: 353
Registered: December 2002

Senior Member

Higher effective date.

I was told this:

Rules: Lowest student career number (STDNT_CAR_NBR) that has the lowest plan sequence (PLAN_SEQUENCE) for that stdnt career number (STDNT_CAR_NBR), all of which is based upon the effective dating (EFFDT) with the highest EFFSEQ.

But I believe it's really:

Rules: Lowest student career number (STDNT_CAR_NBR) that has the lowest plan sequence (PLAN_SEQUENCE) for that stdnt career number (STDNT_CAR_NBR), all of which is based upon the HIGHEST effective dating (EFFDT) with the highest EFFSEQ.

Report message to a moderator

Re: Best solution to retrieve result [message #569905 is a reply to message #569904]

Fri, 02 November 2012 20:58

BlackSwan
Messages: 20086
Registered: January 2009

Senior Member

 1  select * from stu_plan
  2  where effseq = ( select max(effseq) from stu_plan)
  3*  and  effdt  = ( select max(effdt ) from stu_plan)
SQL> /

ID          STDNT_CAR_NBR EFFDT                   EFFSEQ ACAD_PLAN  PLAN_SEQUENCE
----------- ------------- ------------------- ---------- ---------- -------------
111111                  1 2012-05-06 00:00:00          3 BIOLB                  1

Report message to a moderator

Re: Best solution to retrieve result [message #569940 is a reply to message #569905]

Sat, 03 November 2012 07:41

Solomon Yakobson
Messages: 1397
Registered: January 2010

Senior Member

Or more effectively:

select  *
   from stu_plan
   where (effseq,effdt) = (
                           select  max(effseq),
                                   max(effdt )
                             from  stu_plan
                          )
/

ID          STDNT_CAR_NBR EFFDT         EFFSEQ ACAD_PLAN  PLAN_SEQUENCE
----------- ------------- --------- ---------- ---------- -------------
111111                  1 06-MAY-12          3 BIOLB                  1

SQL>

SY.

Report message to a moderator

Re: Best solution to retrieve result [message #569943 is a reply to message #569940]

Sat, 03 November 2012 10:19

John Watson
Messages: 3102
Registered: January 2010
Location: Global Village

Senior Member

I ran the two solutions through explain plan on my 11.2.0.3, to see what the CBO came up with.

This is the BS explain plan:

--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |    62 |     9   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL  | STU_PLAN |     1 |    62 |     3   (0)| 00:00:01 |
|   2 |   SORT AGGREGATE    |          |     1 |    13 |            |          |
|   3 |    TABLE ACCESS FULL| STU_PLAN |    16 |   208 |     3   (0)| 00:00:01 |
|   4 |   SORT AGGREGATE    |          |     1 |     9 |            |          |
|   5 |    TABLE ACCESS FULL| STU_PLAN |    16 |   144 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EFFSEQ"= (SELECT MAX("EFFSEQ") FROM "STU_PLAN"
              "STU_PLAN") AND "EFFDT"= (SELECT MAX("EFFDT") FROM "STU_PLAN"
              "STU_PLAN"))

and this is the SY explain plan:

--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |    62 |     9   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL  | STU_PLAN |     1 |    62 |     3   (0)| 00:00:01 |
|   2 |   SORT AGGREGATE    |          |     1 |    22 |            |          |
|   3 |    TABLE ACCESS FULL| STU_PLAN |    16 |   352 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("EFFSEQ","EFFDT")= (SELECT MAX("EFFSEQ"),MAX("EFFDT")
              FROM "STU_PLAN" "STU_PLAN"))

Recently, I blogged about how I believe that the cost column of explain plan is useless for plan comparison, and was thoroughly crushed by the responses (particularly Ross, who made me think a lot). But this is a perfect example of the issue: the plans are costed the same, but surely the second solution is better.

Report message to a moderator