Avoid select min/max with Oracle

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, 'passed', '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
max(rownum) keep (dense_rank first order by student) AS id,
student,
max(year) year,
max(status) status,
max(program) program
FROM test
GROUP BY student

And the result is :

ID STUDENT YEAR STATUS PROGRAM
7 clara 2012 passed mstf
5 john 2014 passed bba

You can try it by yourself :

http://sqlfiddle.com/#!4/64c7ce/3/0


Enjoy!