Wednesday, September 12, 2018

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!


Wednesday, February 07, 2018

Download Oracle JDK from command line

Found on the web. You can adapt the url with one grabbed from Oracle JDK download page. What I love here is the fact is send Oracle licence agreement threw the header.


wget --no-cookies \
--no-check-certificate \
--header "Cookie: oraclelicense=accept-securebackup-cookie" \
http://download.oracle.com/otn-pub/java/jdk/8u161-b12/2f38c3b165be4555a1fa6e98c45e0808/jdk-8u161-linux-i586.tar.gz \
-O jdk-8-linux-x64.tar.gz


Enjoy!

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 ...