SQL: How to exclude maximum if another column doesn't match

I'm using Oracle 10g. My research at

SQL - How to select a row having a column with max value

and

http://jan.kneschke.de/projects/mysql/groupwise-max/

address what to do with max(row) and then finding other max(rows) But, I'm not there yet.

Criteria: Code for ID's max(enroll_date) must be 'WHEAT'.

I don't want the maximum enroll date where the code is 'WHEAT'. I want the row only if the code is 'WHEAT' for the maximum enroll date.

Question: How can I write query to meet criteria?

This doesn't work because it returns ID=30, C开发者_Python百科ode = WHEAT where Date 12/25/2001 which is not the max(date) for that ID.

    select ID, code, max(enroll_date)
    from enrollment
    where  CODE = 'WHEAT'
    group by ID, code

This doesn't work either because it too returns ID=30, Code = WHEAT, where date = 12/25/2001.

    select ID, code, max(enroll_date)
    from enrollment
    group by ID, code
    having code='WHEAT'

Here's my TABLE

ENROLLMENT
===========================
ID  CODE     ENROLL_DATE
--------------------------
01  WHEAT   <NULL>
01  WHEAT   12/21/2007
01  WHEAT   7/30/2009
30  WHEAT   12/25/2001
30  CHAFF   6/14/2010
72  WHEAT   8/20/2002
72  WHEAT   12/7/2007

 DESIRED RESULT 
 ID    CODE ENROLL_DATE
 ================================
  01     WHEAT  7/30/2009
  72     WHEAT  12/7/2007

It sounds like you want something like

SELECT id, 
       code,
       enroll_date
  FROM (SELECT id, 
               code,
               enroll_date,
               rank() over (partition by id 
                                order by enroll_date desc nulls last) rnk
          FROM enrollment)
 WHERE rnk = 1
   AND code = 'WHEAT'

The analytic function RANK assigns the latest ENROLL_DATE for a particular ID a value of 1, the next most recent ENROLL_DATE for that ID a value of 2, etc. You can then refer to the rank and the code in the outer query. If there can be duplicates, you may prefer to use the DENSE_RANK or ROW_NUMBER analytic function instead

You could also write the query

SELECT id, 
       code,
       enroll_date
  FROM (SELECT id, 
               code,
               enroll_date,
               max(enroll_date) over (partition by id) max_enroll_date
          FROM enrollment)
 WHERE enroll_date = max_enroll_date
   AND code        = 'WHEAT'

Please look up "analytic functions" in the Oracle documentation -- this does what you want to do.

Basically, it lets you sort the records returned from the query in groups, but still give you access to all the columns from the row-- you just want to check that the first row in a group ordered by enroll_date has a code of 'WHEAT'

This simple query produces the desired results

select * from t where enrolldate=
(select max(enrolldate) from t as t1
  where t.id=t1.id)
  and t.code='WHEAT'

For the purposes of your query, you wish you had a separate table that only contained the maximum enrollment date rows. So, you can accomplish that by using a subquery:

SELECT ID, Code, EnrollDate
FROM enrollment a
INNER JOIN (SELECT ID, MAX(EnrollDate) AS MaxEnrollDate
            FROM enrollment
            GROUP BY ID) b
ON (a.ID = b.ID AND a.EnrollDate = b.MaxEnrollDate)
WHERE Code = 'WHEAT'

Hopefully I've done this properly. Even if I haven't, the idea should work.