sql - How to select records with maximum values in two columns?

Question

It was hard to come up with an understandable title for this question. I'll try to explain with an example.

First I had a simple table INFO in Oracle DB:

year      type      message
----      ----      -------
2001      1         cakes are yammy
2003      2         apples are dangerous
2012      2         bananas are suspicious
2005      3         cats are tricky

And I need to select newest messages of certain types (for example type = 1 or type = 2):

2001      1         cakes are yammy
2012      2         bananas are suspicious

So I used the query:

select * from INFO i 
where year = (select max(year) from INFO i_last where i.type = i_last.type) 
and i.type in (1, 2)

But now I need to add a new "quarter" column to my INFO table. And select the newest records by year and quarter.

year      quarter      type      message
----      -------      ----      -------
2001      2            1         cakes are yammy
2012      3            2         onions are cruel
2012      1            2         bananas are suspicious
2005      1            3         cats are tricky

The newest records with type 1 or 2 will be:

2001      2            1         cakes are yammy
2012      3            2         onions are cruel

How should such query look like?

Answer 1

Analytic functions are your friend:

SELECT   MAX( year    ) KEEP ( DENSE_RANK LAST ORDER BY year ASC, quarter ASC, message ASC ) AS year,
         MAX( quarter ) KEEP ( DENSE_RANK LAST ORDER BY year ASC, quarter ASC, message ASC ) AS quarter,
         MAX( message ) KEEP ( DENSE_RANK LAST ORDER BY year ASC, quarter ASC, message ASC ) AS message,
         type
FROM     info
GROUP BY type;

SQLFIDDLE