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

Question

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, Code = 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

Justin Cave · Answer 1 · 2011-06-23T14:03:58.873

7

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'

edited Jun 23 '11 at 14:03

answered Jun 22 '11 at 22:03

Justin Cave

227,342
24
367
384

Sorry for the perhaps-silly question, but is this an Oracle-specific solution? I'd never seen this before and it is kind of awesome. – Chris Cunningham Jun 23 '11 at 01:03
No, not Oracle specific. For example, in MS-SQL Server: http://msdn.microsoft.com/en-us/library/ms173454.aspx. It's categorized under "Aggregate Functions". – DCookie Jun 23 '11 at 01:43
Your first query, returns the null date for ID=01 instead of the max(non-null-date){07/30/2009}. Your second query does return the results I was expecting. Thanks for the suggestions. – lamarz Jun 23 '11 at 13:16
@lamarz - Sorry about that. Forgot the `NULLS LAST` clause in my `ORDER BY`. I updated the query. – Justin Cave Jun 23 '11 at 14:04
@Justin Cave: You and Antlersoft just made my work easier by exposing me to Oracle-Analytics. I've just touched the surface of what these tools can do and I'm impressed. Thanks for your insight and suggestions. – lamarz Jul 13 '11 at 14:08

score 2 · Answer 2 · answered Jun 22 '11 at 21:48

2

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'

answered Jun 22 '11 at 21:48

antlersoft

14,636
4
35
55

Link: [link]http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions001.htm#i88893 – antlersoft Jun 22 '11 at 21:52
Thanks for exposing me to Oracle-Analytics. I've just begun to appreciate the utility of these tools. My work just became easier. – lamarz Jul 13 '11 at 14:11

score 1 · Answer 3 · answered Jun 22 '11 at 22:04

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.

score 1 · Accepted Answer · answered Jun 22 '11 at 23:48

1

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'

answered Jun 22 '11 at 23:48

josephj1989

9,509
9
48
70

This query produced the results, I was expecting. Thanks – lamarz Jun 23 '11 at 13:36
Be aware that writing the query this way is potentially much less efficient than the analytic function approach since it requires hitting the table twice rather than once. – Justin Cave Jul 13 '11 at 14:29
@Justin Cave: I agree. I was under deadline and speed was not an issue. This solution worked, I understood it, and was not familiar with analytic functions. I'm comfortable with analytic functions now. Should I need to revisit this code due to efficiency issues, I've got a ready solution. This exercise certainly broadened my skill set. – lamarz Jul 13 '11 at 15:39

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

4 Answers4