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

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 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