Home » SQL & PL/SQL » SQL & PL/SQL » Best solution to retrieve result (Oracle 11.2 Windows)
Best solution to retrieve result [message #569902] Fri, 02 November 2012 20:18 Go to next message
Duane
Messages: 398
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 Go to previous messageGo to next message
BlackSwan
Messages: 23029
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"?
Re: Best solution to retrieve result [message #569904 is a reply to message #569903] Fri, 02 November 2012 20:55 Go to previous messageGo to next message
Duane
Messages: 398
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.
Re: Best solution to retrieve result [message #569905 is a reply to message #569904] Fri, 02 November 2012 20:58 Go to previous messageGo to next message
BlackSwan
Messages: 23029
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

Re: Best solution to retrieve result [message #569940 is a reply to message #569905] Sat, 03 November 2012 07:41 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2060
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.
Re: Best solution to retrieve result [message #569943 is a reply to message #569940] Sat, 03 November 2012 10:19 Go to previous messageGo to next message
John Watson
Messages: 4793
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.
Re: Best solution to retrieve result [message #569946 is a reply to message #569943] Sat, 03 November 2012 10:36 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2060
Registered: January 2010
Senior Member
I don't see same costs 9 + 3 + 3 + 3 = 18 while 9 + 3 + 3 = 15.

SY.
Re: Best solution to retrieve result [message #569954 is a reply to message #569946] Sat, 03 November 2012 13:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The Cost of SELECT STATEMENT line is the final Cost estimation for the statement.

Regards
Michel
Re: Best solution to retrieve result [message #570023 is a reply to message #569954] Sun, 04 November 2012 15:41 Go to previous message
Duane
Messages: 398
Registered: December 2002
Senior Member
Thanks everyone.
Previous Topic: ora 00001 unique contraint hr.jhist_emp_id_st_date_pk violated
Next Topic: significance of order by clause
Goto Forum:
  


Current Time: Sat Nov 22 23:55:28 CST 2014

Total time taken to generate the page: 0.08189 seconds