Return Only Most Recent Entry Per Id

I'm running this SQL script on my Firebird 2.5-DB: SELECT aktivitaet.creationdatetime, (select STRINGPROPVALUE from PROPERTY WHERE PROPERTYNAME LIKE 'GlobalDokPfad') as basispfad,

Solution 1:

When faced with multiple results from a join and you want to match the most recently dated row, a correlated subquery in the join criteria is one I have found to be quite simple to write and quite fast in execution:

...
JOIN    aktivitaet  a
    ON  a.bold_id = al.aktivitaeten
    AND a.creationdatetime =(
        SELECTMax( creationdatetime )
        FROM    aktivitaet
        WHERE   bold_id = a.bold_id )
...

Solution 2:

Quoting the MySQL manual (though it's valid for all DBMS implementing the SQL '92 Standard):

The Rows Holding the Group-wise Maximum of a Certain Column

Task: For each article, find the dealer or dealers with the most expensive price.

This problem can be solved with a subquery like this one:

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECTMAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);

+---------+--------+-------+| article | dealer | price |+---------+--------+-------+|0001| B      |3.99||0002| A      |10.99||0003| C      |1.69||0004| D      |19.95|+---------+--------+-------+

The preceding example uses a correlated subquery, which can be inefficient (see Section 13.2.10.7, “Correlated Subqueries”). Other possibilities for solving the problem are to use an uncorrelated subquery in the FROM clause or a LEFT JOIN.

Uncorrelated subquery:

SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
  SELECT article, MAX(price) AS price
  FROM shop
  GROUPBY article) AS s2
  ON s1.article = s2.article AND s1.price = s2.price;

LEFT JOIN:

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFTJOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article ISNULL;

The LEFT JOIN works on the basis that when s1.price is at its maximum value, there is no s2.price with a greater value and the s2 rows values will be NULL.