CODEWITHSUNDEEP
sqlfirebirdgreatest-n-per-groupfirebird2.5
moe
moe

Reputation: 13

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, 
aktivitaet.pfad, 
cast(rechnung.datum as date),
rechnung.nummer, 
projekt.code, 
cast(rechnung.verrtotal as numeric(10,2)), 
projekt.betreffend 
FROM rechnung 
INNER JOIN aktivitaetenlink ON rechnung.bold_id=aktivitaetenlink.eintraege 
INNER JOIN aktivitaet ON aktivitaetenlink.aktivitaeten=aktivitaet.bold_id 
Left JOIN projekt ON aktivitaet.projekt=projekt.bold_id 
where rechnung.datum >= '01.01.2013' and rechnung.verrechnet=1 and aktivitaet.typ=15104 
order by rechnung.nummer, aktivitaet.creationdatetime DESC;

It returns me this result:

CREATIONDATETIME,BASISPFAD,PFAD,CAST,NUMMER,CODE,CAST,BETREFFEND 
"14.05.2014 16:53:25";"C:\Daten\Demo\Dokumente";"C:\Daten\Demo\Dokumente\Comte AG,Biel-Bienne\100027_2.doc";"18.12.2014 00:00:00";"100027";;"9491.0499999999993"; 
"14.05.2014 16:53:01";"C:\Daten\Demo\Dokumente";"C:\Daten\Demo\Dokumente\Comte AG, Biel-Bienne\100027.doc";"18.12.2014 00:00:00";"100027";;"9491.0499999999993"; 
"17.10.2014 14:06:08";"C:\Daten\Demo\Dokumente";"C:\Daten\Demo\Dokumente\Baumann GmbH, Luzern\100031.doc";"17.10.2014 00:00:00";"100031";;"842.39999999999998"; 
"16.04.2015 09:51:03";"C:\Daten\Demo\Dokumente";"C:\Daten\Demo\Dokumente\Comte AG, Biel-Bienne\100033.doc";"01.04.2015 00:00:00";"100033";;"1080012.05";
"01.04.2015 16:47:58";"C:\Daten\Demo\Dokumente";"C:\Daten\Dokumente\100033.doc";"01.04.2015 00:00:00";"100033";"COMTE AG";"1080012.05";"Treuhand" 
"15.04.2015 09:24:46";"C:\Daten\Demo\Dokumente";"100035.doc";"15.04.2015 00:00:00";"100035";;"3240.";

My Problem:

If there exist 2 records with the same rechnung.nummer (i.e 1000033 / 1000027), only one record (the one with the last aktivitaet.creationdatetime) should be in the result.

Upvotes: 1

Views: 158

Answers (2)

TommCatt
TommCatt

Reputation: 5636

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 =(
        SELECT  Max( creationdatetime )
        FROM    aktivitaet
        WHERE   bold_id = a.bold_id )
...

Upvotes: 1

fancyPants
fancyPants

Reputation: 51928

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=(SELECT MAX(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
  GROUP BY 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
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL;

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.

Upvotes: 1

Related Questions