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