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.
Thanks a lot. This is obviosly my problem - I need to return the max date for each of the OBJECT_ID in the example query.
Any good examples on how to use the DENSE_RANK() over () ?
I'll try that - thanks a lot again,
I tired to use DENSE_RANK() but I still do not get the number of rows I need. I need to get back only the last (most recent) record of the each objects_name/type in the table.
Thanks a lot.
Any good examples on how to use the DENSE_RANK() over () ?look in doc or search the forum, there were a lot of examples.
I need to get back onlypay attention to PARTITION BY part in analytic functions.
the last (most recent) record of the each
objects_name/type in the table.
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
I still need help on this query. I also tried to use inner join on table and inline view which perform better but aI still pull the whole view twice.
Any otehr ideas?
Thanks a lot, tom
something along the lines of:
SELECT col1, col2 , col3should do what you want.
FROM (SELECT col1, col2 , col3,
DENSE_RANK() OVER(PARTITION BY col1, col2
ORDER BY col3 DESC) rn
where 1,2,3 and
rn = 1
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: