Date of max id: sql/oracle optimization

问题:

What is a more elegant way of doing this:

select date from table where id in (
  select max(id) from table);

Surely there is a better way...

回答1:

select date from (select date from table order by id desc) 
where rownum < 2

assuming your ids are unique.

EDIT: using subquery + rownum

回答2:

You can use the ROWNUM pseudocolumn. The subquery is necessary to order the result before finding the first row:

SELECT date 
FROM (SELECT * FROM table ORDER BY id DESC)
WHERE ROWNUM = 1;

You can use subquery factoring in Oracle 9i and later in the following way:

WITH ranked_table AS (
    SELECT ROWNUM AS rn, date
    FROM table
    ORDER BY id DESC
)
SELECT date FROM ranked_table WHERE rn = 1;

You can use a self-join, and find where no row exists with a greater id:

SELECT date
FROM table t1
LEFT OUTER JOIN table t2
  ON t1.id < t2.id
WHERE t2.id IS NULL;

Which solution is best depends on the indexes in your table, and the volume and distribution of your data. You should test each solution to determine what works best, is fastest, is most flexible for your needs, etc.