Knocking down a SELECT statement

Question

First, I will show you example tables to which my problem relates, then I will ask a 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 the values ​​fruit_name, fruit_id and fruit_owner. I want to get only one row for each fruit, and the way I want it will be decided by the last time stamp. For example, the correct query in this table will return:

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

I tried the request:

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 problem with this is returning mainly the excellent names of fruits, 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 the use of self join in 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

, , 2+ .

Answer 2

, fruit_.

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

-, .

Answer 3

Try the subquery:

select a.fruit_name, a.fruit_id, a.fruit_owner
from my_fruits a
where a.fruit_timestamp =
 (select max(b.fruit_timestamp)
  from my_fruits b
  where b.fruit_id = a.fruit_id)

Answer 4

I would do this by finding out a list of your interests (fruit_name, fruit_timestamp), and then grouping this “table” with the actual fruit table and getting other values.

SELECT fruit_and_max_t.fruit_name, 
       my_fruits.fruit_id,
       my_fruits.fruit_owner
FROM my_fruits, 
  ( SELECT fruit_name, MAX(fruit_timestamp) AS max_timestamp
    FROM my_fruits
    GROUP BY fruit_name) AS fruit_and_max_t,
WHERE fruit_and_max_t.max_timestamp = my_fruits.fruit_timestamp
  AND fruit_and_max_t.fruit_name    = my_fruits.fruit_name

This assumes that the table does not have several records with the same value (fruit_name, fruit_timestamp), i.e. this tuple (pair) acts as a unique identifier.