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.
精彩评论