Can I delete the most recent record without sub-select in Oracle?

I want a SQL statement to delete the most recent record in a table. Here's my idea:

delete from daily_statistics 
wh开发者_运维技巧ere process_date = (
  select max(process_date) 
  from daily_statistics
);

But it seems like there is likely a way to do this without a sub-select, which might be inefficient. (Efficiency isn't actually important in my case, I just want to know the simplest, most readable way to code this.)

The most readable way is probably what you wrote. But it can be very wasteful depending on various factors. In particular, if there is no index on process_date it likely has to do 2 full table scans.

The difficulty in writing something that is both simple and more efficient, is that any view of the table that includes a ranking or ordering will also not allow modifications.

Here's one alternate way to approach it, using PL/SQL, that will probably be more efficient in some cases but is clearly less readable.

DECLARE
  CURSOR delete_cur IS
    SELECT /*+ FIRST_ROWS(1) */
      NULL
    FROM daily_statistics
    ORDER BY process_date DESC
    FOR UPDATE;
  trash  CHAR(1);
BEGIN
  OPEN delete_cur;
  FETCH delete_cur INTO trash;
  IF delete_cur%FOUND THEN
    DELETE FROM daily_statistics WHERE CURRENT OF delete_cur;
  END IF;
  CLOSE delete_cur;
END;
/

Also note this potentially produces different results from your statement if there can be multiple rows with the same process_date value. To make it handle duplicates requires a little more complexity:

DECLARE
  CURSOR delete_cur IS
    SELECT /*+ FIRST_ROWS(1) */
      process_date
    FROM daily_statistics
    ORDER BY process_date DESC
    FOR UPDATE;
  del_date  DATE;
  next_date DATE;
BEGIN
  OPEN delete_cur;
  FETCH delete_cur INTO del_date;
  IF delete_cur%FOUND THEN
    DELETE FROM daily_statistics WHERE CURRENT OF delete_cur;
  END IF;
  LOOP
    FETCH delete_cur INTO next_date;
    EXIT WHEN delete_cur%NOTFOUND OR next_date <> del_date;
    DELETE FROM daily_statistics WHERE CURRENT OF delete_cur;
  END LOOP;
  CLOSE delete_cur;
END;
/

I knew there was a better way that I wasn't thinking of.

delete from daily_statistics 
where rowid = (
  select max(rowid) keep (dense_rank first order by process_date desc) 
  from daily_statistics
);

Again, this will only delete a single row, even if there are multiple rows with the maximum value, so depending on your data it can produce different results than the original query.