This content has been marked as final. Show 8 replies
I tried to use "ROW_NUMBER() OVER (ORDER BY dateprobably you wanted to sort on date DESC, so that max date came first, and then use where rn=1 in the outer query.
ASC) AS row_number, " rather to select the max(date)
in teh sub query but I get only 1 records returned by
the query instead the expected about 25,000.
but don't forget, if you have several rows with max date - row_number() will assign row_number=1 only to the first one (probably randomly, if you don't have any other sorting).
In your case you need to use DENSE_RANK() over () analytic function.
I got the query to run - with dense ranck and row-Number I get the same results. My partition by cluse was wrong. But the perfromance is not much better.
Any other idea on how to optimize a query that uses pattern like below and needs to return all rows from a table that have the max enter date.
Thanks a lot, tom
col1 col2 col3
1 sysdate 'I need this record'
2 sysdate 'I need this record'
3 sysdate 'I need this record'
4 sysdate 'I do NOT need this record'
5 sysdate 'I do NOT need this record'
5 sysdate+3 'I need this record'
5 sysdate+2 'I do NOT need this record'
4 sysdate+3 'I need this record'
4 sysdate+2 'I do NOT need this record'
4 sysdate+1 'I do NOT need this record'
My query corrently is using the following construcion:
select col1, col2 , col3
from test_table t
and col3 = (select max(col3) from test_table t2 where t2.col1 = t.col1) group by t2.col1
What is your oracle database version?
From 10g onwards you should be able to use the KEEP clause of the aggregate functions.
Something like (not sure about correct syntax, since I'm on a 9i database currently).
select id, max(date) keep (dense rank first other columns)
group by ID
Just noticed that Maxim gave a very nice example in this other thread: Grouping - beginner's question
Message was edited by: