Help with SQL query - some magic needed

I need some help with a query - I'm using Firebird 2.1.

I have a table like:

RowID (primary key) | ActivityID | Duration | BilledAt

1 | 1 | 50 | 06.08.2010, 14:05:00.598
2 | 1 | 70 | 06.08.2010, 14:05:00.608
3 | 2 | 30 | 06.08.2010, 14:05:00.598
4 | 3 | 40 | 06.08.2010, 14:05:00.598
5 | 3 | 50 开发者_JS百科| 06.08.2010, 14:05:00.608

I'd like to get the Durations for each ActivityID BUT if there are more than one entries available with the same ActivityID, I need the get the one with the highest BilledAt value. (the most recent entry)

If I execute:

SELECT ActivityID, Max(BilledAt) 
FROM BilledTime 
GROUP BY ActivityID;

I'll get what I want without the Duration values. If I include the Duration column in the GROUP BY clause, then multiple ActivityIDs are selected.

Is there an elegant solution to this?

Thanks!

Not familiar with Firebird so the syntax might be wrong, but this should work:

SELECT a.ActivityID, a.Duration, a.BilledAt
FROM BilledTime a
LEFT JOIN BilledTime b on a.ActivityID = b.ActivityID AND b.BilledAt > a.BilledAt
WHERE b.ActivityID IS NULL

Alternatively you can use a more intuitive WHERE NOT EXISTS subquery instead of the LEFT JOIN, but I believe the above ends up being faster.

I would do like this

SELECT a.ActivityID, a.Duration, a.BilledAt 
FROM BilledTime a 
WHERE a.BilledAt = (select max(b.billedAt) from BilledTime b where b.ActivityId = a.ActivityID)