CODEWITHSUNDEEP
oracle-database
user4087083
user4087083

Reputation: 13

How to get Last Record from Oracle without using RowID?

I have 1 column table which is following:

Time_Minutes
6.950
6.967
6.083

I am getting these value after writing 2 pages big query in Oracle. From this, I want last record which is 6.083

But the issue is, I can not use ROWID as I am using order by and distinct in big query. What are other approaches?

Also, I can not use Order By Logic.

Upvotes: 0

Views: 800

Answers (2)

sunmoon
sunmoon

Reputation: 13

try analytical function.

SELECT * FROM 
    (SELECT a,
            rank() over (order by a) rk, 
            count(*) over() cnt
     from tablename
    ) where rk = ct

Upvotes: 0

Dave Costa
Dave Costa

Reputation: 48111

By "last record" do you mean "most recently inserted record"?

First of all, please forget the notion that ROWID would ever be useful to get this result. Relying on ROWIDs to increase as rows are inserted into a table is asking for trouble. Rows can be deleted and new rows inserted where the old row was. Segments can extend and allocate extents that will have "lower" ROWID values than the previous extents. Partitioning, row movement, any number of other features will mean that ROWIDs don't necessarily increase as new rows are inserted.

You need to have another column that tells you the order in which the rows are inserted, if that information is important to you. Personally I would have a DATE or TIMESTAMP, which can be automatically populated with a trigger. You could also use an ID column populated from an increasing sequence.

The only sneaky option I can think of is to use flashback versions query to figure out which row is the newest, but that would become fairly complex.

Upvotes: 3

Related Questions