An Example of Using MAX(LEAST...) in an SQL Statement

This is an example of how to use MAX LEAST in an SQL statement.


First, to show what the raw data looks like. If I run this query for 014117910(without the MAX LEAST and groupings)

SELECT A.EMPLID

  , B.AID_YEAR

  , A.NSLDS_LOAN_TYPE

  , A.NSLDS_LOAN_YEAR

  , SUBSTR( A.NSLDS_SCHOOL_CD

  ,1

  ,6)

  , A.NSLDS_PRIN_BAL

  , A.NSLDS_TOT_DISB_AMT

  FROM PS_NSLDS_ISIR_DTL A

   , PS_ISIR_CONTROL B

  WHERE  A.EMPLID = '014117910'

   AND  A.NSLDS_LOAN_START >= '01-MAY-10'

    AND A.NSLDS_LOAN_START <= '01-AUG-11'

    AND A.NSLDS_SCHOOL_CD <> 'xxxxx000'

    AND A.EMPLID = B.EMPLID

    --and A.NSLDS_PRIN_BAL <> A.NSLDS_TOT_DISB_AMT

    AND B.EFFDT = (

  SELECT MAX(B_ED.EFFDT)

   FROM PS_ISIR_CONTROL B_ED

  WHERE B.EMPLID = B_ED.EMPLID

    AND B.INSTITUTION = B_ED.INSTITUTION

    AND B.AID_YEAR = B_ED.AID_YEAR

    AND B_ED.EFFDT <= SYSDATE)

    AND B.EFFSEQ = (

  SELECT MAX(B_ES.EFFSEQ)

   FROM PS_ISIR_CONTROL B_ES

  WHERE B.EMPLID = B_ES.EMPLID

    AND B.INSTITUTION = B_ES.INSTITUTION

    AND B.AID_YEAR = B_ES.AID_YEAR

    AND B.EFFDT = B_ES.EFFDT)

    AND B.AID_YEAR = '2010'

   AND (( A.NSLDS_LOAN_TYPE = 'D1'

    AND NOT EXISTS (

  SELECT 'X'

   FROM PS_STDNT_AGGR_SCHL C

  WHERE C.EMPLID = B.EMPLID

    AND C.AID_YEAR = B.AID_YEAR

    AND C.AGGREGATE_AREA = 'DLSUB'

    AND C.CPS_SCHOOL_CODE <> 'xxxxx0'

    AND C.AGGREGATE_STATUS = 'I'))

     OR ( A.NSLDS_LOAN_TYPE = 'D2'

    AND NOT EXISTS (

  SELECT 'X'

   FROM PS_STDNT_AGGR_SCHL D

  WHERE D.EMPLID = B.EMPLID

    AND D.AID_YEAR = B.AID_YEAR

    AND D.AGGREGATE_AREA = 'DLUNSUB'

    AND D.CPS_SCHOOL_CODE <> 'xxxxx0'

    AND D.AGGREGATE_STATUS = 'I')))

    AND A.NSLDS_TOT_DISB_AMT > 0

    AND A.NSLDS_PRIN_BAL > 0

    AND EXISTS (

  SELECT 'X'

   FROM PS_STDNT_AGGR_LIFE E

  WHERE E.EMPLID = B.EMPLID

    AND E.AID_YEAR = B.AID_YEAR

    AND E.AGGREGATE_AREA LIKE 'DL%')

    order  BY A.EMPLID, B.AID_YEAR, A.NSLDS_LOAN_TYPE, A.NSLDS_LOAN_YEAR, SUBSTR( A.NSLDS_SCHOOL_CD,1,6) ;

I get the following results:

Then, when I run the query for the same ID, but with the MAX LEAST logic, including GROUPing:

SELECT A.EMPLID

  , B.AID_YEAR

  , A.NSLDS_LOAN_TYPE

  , A.NSLDS_LOAN_YEAR

  , SUBSTR( A.NSLDS_SCHOOL_CD

  ,1

  ,6)

  , MAX( LEAST ( A.NSLDS_PRIN_BAL

  , A.NSLDS_TOT_DISB_AMT))

   FROM PS_NSLDS_ISIR_DTL A

   , PS_ISIR_CONTROL B

  WHERE A.EMPLID = '014117910'

   AND A.NSLDS_LOAN_START >= '01-MAY-10'

    AND A.NSLDS_LOAN_START <= '01-AUG-11'

    AND A.NSLDS_SCHOOL_CD <> 'xxxxx000'

    AND A.EMPLID = B.EMPLID

    AND B.EFFDT = (

  SELECT MAX(B_ED.EFFDT)

   FROM PS_ISIR_CONTROL B_ED

  WHERE B.EMPLID = B_ED.EMPLID

    AND B.INSTITUTION = B_ED.INSTITUTION

    AND B.AID_YEAR = B_ED.AID_YEAR

    AND B_ED.EFFDT <= SYSDATE)

    AND B.EFFSEQ = (

  SELECT MAX(B_ES.EFFSEQ)

   FROM PS_ISIR_CONTROL B_ES

  WHERE B.EMPLID = B_ES.EMPLID

    AND B.INSTITUTION = B_ES.INSTITUTION

    AND B.AID_YEAR = B_ES.AID_YEAR

    AND B.EFFDT = B_ES.EFFDT)

    AND B.AID_YEAR = '2010'

   AND (( A.NSLDS_LOAN_TYPE = 'D1'

    AND NOT EXISTS (

  SELECT 'X'

   FROM PS_STDNT_AGGR_SCHL C

  WHERE C.EMPLID = B.EMPLID

    AND C.AID_YEAR = B.AID_YEAR

    AND C.AGGREGATE_AREA = 'DLSUB'

    AND C.CPS_SCHOOL_CODE <> 'xxxxx0'

    AND C.AGGREGATE_STATUS = 'I'))

     OR ( A.NSLDS_LOAN_TYPE = 'D2'

    AND NOT EXISTS (

  SELECT 'X'

   FROM PS_STDNT_AGGR_SCHL D

  WHERE D.EMPLID = B.EMPLID

    AND D.AID_YEAR = B.AID_YEAR

    AND D.AGGREGATE_AREA = 'DLUNSUB'

    AND D.CPS_SCHOOL_CODE <> 'xxxxx0'

    AND D.AGGREGATE_STATUS = 'I')))

    AND A.NSLDS_TOT_DISB_AMT > 0

    AND A.NSLDS_PRIN_BAL > 0

    AND EXISTS (

  SELECT 'X'

   FROM PS_STDNT_AGGR_LIFE E

  WHERE E.EMPLID = B.EMPLID

    AND E.AID_YEAR = B.AID_YEAR

    AND E.AGGREGATE_AREA LIKE 'DL%')

   GROUP BY A.EMPLID, B.AID_YEAR, A.NSLDS_LOAN_TYPE, A.NSLDS_LOAN_YEAR, SUBSTR( A.NSLDS_SCHOOL_CD,1,6)

   ORDER BY A.EMPLID

I get the following:

 

In the first result set, you might have noticed that there ARE two rows where the LEAST is less than 18222, but there is one row where the LEAST is 18222, so it’s the MAX LEAST for the first record that is returned based on the GROUP BY clause.