Clash Royale CLAN TAG#URR8PPP

Order By clause returning different result when querying each time

I have a table Exc with following columns in it.

TIME DATE
CODE CHAR(9 BYTE)
VALUE NUMBER(5,2)


There was no sequence number or primary key in the table.
Since there is no unique ID in the table, I have written the following query to generate the unique number for each row which is needed to identify each row as unique.

select time, code, value
from (select time, code, value, ROW_NUMBER() over (order by time) R from EXC) where R > :x;


x - variable to hold the last maximum row number.

But when I run the above query for the second time, the order by index time is returning in a different order for the rows with same time. I need to have a consistent order by from the Oracle query.

Kindly help me on this issue.

2 Answers
2

a different order for the rows with same time

If time isn't unique then you need to decide how to break ties. We can't see your data but this should at least be consistent:

time

select time,code,value, ROW_NUMBER() over (order by time, code, value) R
from EXC;


It seems odd that you are generating the R value in the inner query but then not using it in the outer query. If you only want to use it for ordering the results then you could do:

R

select time, code, value
from (
select time, code, value, ROW_NUMBER() over (order by time, code, value) R
from EXC
)
order by r;


or just:

select time, code, value
from EXC
order by time, code, value;


If you do actually want to see the R value too then you don't need a subquery:

R

select time, code, value, ROW_NUMBER() over (order by time, code, value) R
from EXC
order by r;


or even using then Oracle-only rownum pseudocolumn:

rownum

select time, code, value, rownum R
from EXC
order by order by time, code, value;


I need the R value to fetch the data next time by using ...

You can combine this with a filter, pretty much as you showed:

select time, code, value
from (
select time, code, value, ROW_NUMBER() over (order by time, code, value) R
from EXC
)
where r > :x
order by r;


But if you're mostly using this to look for recent data you might be better off recording the time and filtering directly on that:

select time, code, value
from EXC
where time > :x
order by ...


taking care how you maintain that variable of course.

Try this:

SELECT time, code, VALUE
FROM (SELECT time, code, VALUE, ROW_NUMBER () OVER (ORDER BY time) R FROM EXC)
ORDER BY r ASC;


It orders output by your rownum.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.