Best way to select the row with the most recent timestamp that matches a criterion

Question

This is something that comes up so often I almost stopped thinking about it but I'm almost certain that I'm not doing this the best way.

The question: Suppose you have the following table

CREATE TABLE TEST_TABLE
(
  ID          INTEGER,
  TEST_VALUE  NUMBER,
  UPDATED     DATE,
  FOREIGN_KEY INTEGER
);

What is the best way to select the TEST_VALUE associated with the most recently updated row where FOREIGN_KEY = 10?

EDIT: Let's make this more interesting as the answers below simply go with my method of sorting and then selecting the top row. Not bad but for large returns the order by would kill performance. So bonus points: how to do it in a scalable manner (ie without the unnecessary order by).

Answer 1

The probably inferior way that I currently go about doing something like this is

SELECT TEST_VALUE
FROM TEST_TABLE
WHERE ID = (
  SELECT ID
  FROM (
    SELECT ID
    FROM TEST_TABLE
    WHERE FOREIGN_KEY = 10
    ORDER BY UPDATED DESC
  )
  WHERE ROWNUM = 1
)

but please StackOverflow Geniuses, teach me some tricks

Answer 2

Either use a sub-query

WHERE updated = (SELECT MAX(updated) ...)

or select the TOP 1 record with

ORDER BY updated DESC

In Oracle syntax this would be:

SELECT 
  * 
FROM 
(
  SELECT * FROM test_table
  ORDER BY updated DESC
)
WHERE 
  ROWNUM = 1

Answer 3

select test_value
from
(
  select test_value 
  from test_table
  where foreign_key=10
  order by updated desc
)
where rownum = 1

Oracle is smart enough to realize it only needs a single row from the inner select and it will do this efficiently.

Answer 4

wouldn't this work:

SELECT TOP 1 ID
FROM test_table
WHERE FOREIGN_KEY = 10
ORDER BY UPDATED DESC

no need for a subquery...

Answer 5

Analytic functions are your friends

SQL> select * from test_table;

        ID TEST_VALUE UPDATED   FOREIGN_KEY
---------- ---------- --------- -----------
         1         10 12-NOV-08          10
         2         20 11-NOV-08          10

SQL> ed
Wrote file afiedt.buf

  1* select * from test_table
SQL> ed
Wrote file afiedt.buf

  1  select max( test_value ) keep (dense_rank last order by updated)
  2  from test_table
  3* where foreign_key = 10
SQL> /

MAX(TEST_VALUE)KEEP(DENSE_RANKLASTORDERBYUPDATED)
-------------------------------------------------
                                               10

You can also extend that to get the information for the entire row

SQL> ed
Wrote file afiedt.buf

  1  select max( id ) keep (dense_rank last order by updated) id,
  2         max( test_value ) keep (dense_rank last order by updated) test_value
,
  3         max( updated) keep (dense_rank last order by updated) updated
  4  from test_table
  5* where foreign_key = 10
SQL> /

        ID TEST_VALUE UPDATED
---------- ---------- ---------
         1         10 12-NOV-08

And analytic approaches are generally pretty darned efficient.

I should also point out that analytic functions are relatively new, so if you are on something earlier than 9.0.1, this may not work. That's not a huge population any more, but there are always a few folks stuck on old versions.

Answer 6

Firstly, you will always need to look at all the rows with that foreign key, and find the one with the highest UPDATED value...which means a MAX or ORDER BY. The efficiency of the comparison is partly up to the optimizer, so will depend on your Oracle version. Your data structures may have a greater impact on actual performance though. An index on FOREIGN_KEY, UPDATED DESC, TEST_VALUE would probably give the most scalable solution for querying as Oracle will normally be able to give the answer just accessing a single leaf block. There may be a detrimental impact on inserts as new records have to be inserted into that structure.

Answer 7

Performance will depend on what is indexed. Here is a method.

WITH 
ten AS
(
    SELECT *
    FROM TEST_TABLE
    WHERE FOREIGH_KEY = 10
)
SELECT TEST_VALUE 
FROM ten
WHERE UPDATED = 
(
    SELECT MAX(DATE)
    FROM ten
)

Answer 8

There is an oracle SQL faq here that may help you:

http://www.orafaq.com/wiki/SQL_FAQ

Answer 9

SELECT TEST_VALUE
  FROM TEST_TABLE
 WHERE UPDATED      = ( SELECT MAX(UPDATED)
                          FROM TEST_TABLE
                         WHERE FOREIGN_KEY = 10 )
   AND FOREIGN-KEY  = 10
   AND ROWNUM       = 1  -- Just in case records have the same UPDATED date

Rather that take the first record you could break a tie with the hightest ID or maybe least/largest TEST_VALUE.

An index of FOREIGN_KEY, UPDATED would help query performace.