How to fetch latest rows from oracle db [duplicate]

Question

I tried to use max() function to fetch the latest record from my table but I don't know it's a proper way to fetch the latest record from DB or not.

Query

    select max(writime) from my_table;

writime is epoch time like 1210528050500000 . How to get the latest value/ last row. Need help

Answer 1

You can do this two ways:

1. A subquery in the WHERE clause (will return all rows with the max time, may be more than one)
2. Use ORDER BY and ROWNUM to select a row based on criteria you specify (guarantees just one row - but will need to be very specific in order to be deterministic)

Subquery:

SELECT 
  <whatever>
FROM
  my_table
WHERE
  writime =
    (
      SELECT
        MAX(writime)
      FROM
        my_table
    )

As I mentioned earlier, if multiple rows have the same value for writime this query will return all of those rows. If this is your desired output (or acceptable), great!

If not, you'll need to use ROWNUM (see this article for more detail/optimization considerations¹):

SELECT
  *
FROM
  (
    SELECT 
      <whatever>
    FROM
      my_table
    ORDER BY
      writime DESC
     ,<additional sort columns>
  )
WHERE
  ROWNUM = 1

This is guaranteed to return just one row. HOWEVER, this is not guaranteed to return exactly the same row (given the same point in time) unless you provide additional criteria in the ORDER BY statement - generally this would be some columns chosen for business context plus the primary key column(s)² of the table.

You certainly can combine these approaches (which may be better from a performance perspective, depending on what indexes exist on the table and how the data is distributed):

SELECT
  *
FROM
  (
    SELECT 
      <whatever>
    FROM
      my_table
    WHERE
      writime =
        (
          SELECT
            MAX(writime)
          FROM
            my_table
        )
    ORDER BY
      <additional sort columns>
  )
WHERE
  ROWNUM = 1

¹ Because selecting the top N requires a sort of the heap (in the absence of the table organized by writime or a covering index), this can be a very expensive operation. You'll need to look at how your table is structured/indexed to determine what the ideal query is - the Ask Tom article has some methods to achieve this.

² Because the primary key is unique, sorting based on its column(s) will guarantee the same sort order each time.

Answer 2

To return the row with the current highest value of writime, you can just do

select * from my_table
order by writetime desc
fetch first 1 row only

If you want all the rows that share the same maximum writetime value you would use the with ties option:

select * from my_table
order by writetime desc
fetch first 1 row with ties

Few things to consider:

• If the column is nullable, null values will be returned first. You probably want to include an additional where writetime is not null filter to ensure this doesn't happen and also allow an index to be used (not needed if the column is declared not null)
• You can only read rows that have been committed. Some process may have inserted a row with a higher value writetime but has not yet committed, you will be ignoring that when you select from the table.
• It's a bit of a weird query to be writing for an application

Answer 3

The only way is to have a date or timestamp field in your table that is populated by SYSDATE or SYSTIMESTAMP at the time of insertion. Then only can you select the max value of that column.