Confusing SELECT statement

Question

First I will show you example tables that my issue pertains to, then I will ask the question.

[my_fruits]
fruit_name   |   fruit_id   | fruit_owner   |   fruit_timestamp
----------------------------------------------------------------
Banana       |   3          |  Timmy        |   3/4/11
Banana       |   3          |  Timmy        |   4/1/11
Banana       |   8          |  Timmy        |   5/2/11
Apple        |   4          |  Timmy        |   2/1/11
Apple        |   4          |  Roger        |   3/4/11

Now I want to run a query that only selects fruit_name, fruit_id, and fruit_owner values. I only want to get one row per fruit, and the way I want it to be decided is by the latest timestamp. For example the perfect query on this table would return:

[my_fruits]
fruit_name   |   fruit_id   | fruit_owner   |
----------------------------------------------
Banana       |   8          |  Timmy        |
Apple        |   4          |  Roger        | 

I tried the query:

select max(my_fruits.fruit_name) keep 
    (dense_rank last order by my_fruits.fruit_timestamp) fruit_name,
       my_fruits.fruit_id, my_fruits.fruit_owner 
from my_fruits 
group by my_fruits.fruit_id, my_fruits.fruit_owner

Now the issue with that is returns basically distinct fruit names, fruit ids, and fruit owners.

Answer 1

For Oracle 9i+, use:

SELECT x.fruit_name,
       x.fruit_id,
       x.fruit_owner
  FROM (SELECT mf.fruit_name,
               mf.fruit_id,
               mf.fruit_owner,
               ROW_NUMBER() OVER (PARTITION BY mf.fruit_name
                                      ORDER BY mf.fruit_timestamp) AS rank
          FROM MY_FRUIT mf) x
 WHERE x.rank = 1

Most databases will support using a self join on a derived table/inline view:

SELECT x.fruit_name,
       x.fruit_id,
       x.fruit_owner
  FROM MY_FRUIT x
  JOIN (SELECT t.fruit_name,
               MAX(t.fruit_timestamp) AS max_ts
          FROM MY_FRUIT t
      GROUP BY t.fruit_name) y ON y.fruit_name = x.fruit_name
                              AND y.max_ts = x.fruit_timestamp

However, this will return duplicates if there are 2+ fruit_name records with the same timestamp value.

Answer 2

If you want one row per fruit name, you have to group by fruit_name.

select fruit_name,
       max(my_fruits.fruit_id) keep 
          (dense_rank last order by my_fruits.fruit_timestamp) fruit_id,
       max(my_fruits.fruit_owner) keep 
          (dense_rank last order by my_fruits.fruit_timestamp) fruit_owner
from my_fruits 
group by my_fruits.fruit_name

How you want to deal with tie-breaks is a separate issue.