This content has been marked as final. Show 19 replies
Laurent is correct !
But I have a slight issue.
A table is a collection of records.
The concept of "Last Row " is only valid if we implement an ordering mechanism for the collection
However if we do implement an ordering mechanism, then therefore we must be iterating [ in theory ] through the rows to get to the end [ a sort ]
to get the last row then no matter what you do is very close to implementing a count()
"is there any other way without nesting?"
Not correctly, no. What is your problem with nesting? Nested queries are not inherently slower than unnested queries. In some cases, such as this, they are the only correct way to do the query. Even an analytic version of the Top N needs to be nested because:
SQL> SELECT object_nameTTFN
2 FROM t
3 WHERE ROW_NUMBER() OVER (ORDER BY object_name DESC) < 11
WHERE ROW_NUMBER() OVER (ORDER BY object_name DESC) < 11
ERROR at line 3:
ORA-30483: window functions are not allowed here
Is there a way to speed up the query? I run the query in a table with only 35,260 rows and it takes 20secs - 37secs to return the last 10.
Select * from (SELECT ROWNUM
, TO_CHAR(TX_RECE_TIME,'yyyy/MM/dd HH24:mi:ss') REC_TIME
, TO_CHAR(TX_PROCESSED_TIME,'yyyy/MM/dd HH24:mi:ss') PROC_TIME
Where ROWNUM >(select max(rownum)-10 from journal_and_msg_error_333)
It was not the fact that I had not read it but it had not made sense until I went over it 3 more times, rewrote it 10 more times that it now finally works and makes sense.
Not all of us learn by being called a plagueristic idiot. I am new to this and make mistakes. If that is the worst one then hoorah! (I'm just glad I am persistent)
I'll make more along the way. Til the next one :) Toodles and thanks. I have my answer.