Fetching the most recent entry from a log-table

Sometimes there’s a need to keep a simple log in a database. A common format could be a table with a layout like this:

log

• area (char)
• lognotice (char or text)
• logtime (timestamp when the event was logged).

Fetching

Fetching all log entries from a certain area is a simple matter of fetching by the area field, but when building a dashboard with the most recent entry from each area is slightly more complicated - the Query to fetch the data could typically look like this:

SELECT \* FROM log log1
WHERE logtime = (
	SELECT max(logtime) 
	FROM log log2 
	WHERE log2.area = log.area)

Cleaning up

To keep things clean and tidy, I only sorte data from the past month, week or day (depending on the “log intensity”). To achieve this I usually do something like this:

DELETE FROM log WHERE logtime < ###time###