CODEWITHSUNDEEP
sqloracle-databaseoracle11g
KMN
KMN

Reputation: 1

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.

Upvotes: 0

Views: 588

Answers (2)

Alex Poole
Alex Poole

Reputation: 191245

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:

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:

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:

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:

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.

Upvotes: 2

Goran Kutlaca
Goran Kutlaca

Reputation: 2024

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.

Upvotes: 0

Related Questions