I hate select max clauses with Oracle. My company used to write subqueries which such expressions. It's slow and unreadable. Conditions in subquery with upper table create deep dependencies and awfull links between table. It doesn't allow to reuse code.
That's why I investigate on a performant solution to avoid select max/min. A solution is "dense_rank first". It will help you to retreive only one line per group. It's difficult to explain. So, I made a sample on SQL Fiddle.
Schema is :
CREATE TABLE test (student, year, status, program) AS
SELECT 'john', 2010, 'passed', 'bba' FROM DUAL
UNION ALL SELECT 'john', 2011, 'passed', 'bba' FROM DUAL
UNION ALL SELECT 'john', 2012, 'passed', 'bba' FROM DUAL
UNION ALL SELECT 'john', 2013, 'failed', 'bba' FROM DUAL
UNION ALL SELECT 'john', 2014, 'unknown', 'bba' FROM DUAL
UNION ALL SELECT 'clara', 2011, 'failed', 'mstf' FROM DUAL
UNION ALL SELECT 'clara', 2012, 'passed', 'msmmd' FROM DUAL;
Sample request is :
SELECT
student,
min(year) keep (dense_rank FIRST ORDER BY YEAR desc) year,
min(status) keep (dense_rank FIRST ORDER BY YEAR desc) status,
min(program) keep (dense_rank FIRST ORDER BY YEAR desc) program
FROM test
GROUP BY student
And the result is :
STUDENT |YEAR |STATUS |PROGRAM |
--------|-----|--------|--------|
clara |2012 |passed |msmmd |
john |2014 |unknown |bba |
How to write request ?
This is the most important part of this post. In this sample, we want to get the status and program for the most recent academic year for each student. So, first of all, we have to GROUP BY student. Once we have partionned our data by student, we want to keep that contains the most recent academic year. That's why we make a keep (dense_rank FIRST ORDER BY YEAR desc).
So, don't forget :
You can try it by yourself :
http://sqlfiddle.com/#!4/ba828/1/0
Enjoy!
That's why I investigate on a performant solution to avoid select max/min. A solution is "dense_rank first". It will help you to retreive only one line per group. It's difficult to explain. So, I made a sample on SQL Fiddle.
Schema is :
CREATE TABLE test (student, year, status, program) AS
SELECT 'john', 2010, 'passed', 'bba' FROM DUAL
UNION ALL SELECT 'john', 2011, 'passed', 'bba' FROM DUAL
UNION ALL SELECT 'john', 2012, 'passed', 'bba' FROM DUAL
UNION ALL SELECT 'john', 2013, 'failed', 'bba' FROM DUAL
UNION ALL SELECT 'john', 2014, 'unknown', 'bba' FROM DUAL
UNION ALL SELECT 'clara', 2011, 'failed', 'mstf' FROM DUAL
UNION ALL SELECT 'clara', 2012, 'passed', 'msmmd' FROM DUAL;
Sample request is :
SELECT
student,
min(year) keep (dense_rank FIRST ORDER BY YEAR desc) year,
min(status) keep (dense_rank FIRST ORDER BY YEAR desc) status,
min(program) keep (dense_rank FIRST ORDER BY YEAR desc) program
FROM test
GROUP BY student
And the result is :
STUDENT |YEAR |STATUS |PROGRAM |
--------|-----|--------|--------|
clara |2012 |passed |msmmd |
john |2014 |unknown |bba |
How to write request ?
This is the most important part of this post. In this sample, we want to get the status and program for the most recent academic year for each student. So, first of all, we have to GROUP BY student. Once we have partionned our data by student, we want to keep that contains the most recent academic year. That's why we make a keep (dense_rank FIRST ORDER BY YEAR desc).
So, don't forget :
- Group your data by a criteria
- In each group, you will keep only the first row. That's why you have to define a order criteria
- Of course, your "group by" criteria can not be used as "order" criteria
You can try it by yourself :
http://sqlfiddle.com/#!4/ba828/1/0
Enjoy!