Aggregate function returning first row data for column not in the group by

Question

I have a table relationship that looks like the following:

barn
------
PK barn_id
<other columns>

stable
---------
PK stable_id
FK barn_id
stable_number
stable_contents
timestamp

So whenever the contents of a stable change I just put in a new row with the corresponding barn_id and stable_number with new stable_contents and a current timestamp.

The tables are designed this way so I can look at a certain stable and see its entire history.

I am trying to write a query that will find me the current state of all the stables in all the barns, so I try this:

SELECT barn_id, stable_number, max(timestamp), stable_contents
FROM stable
GROUP BY barn_id, stable_number

In my test data I have some rows like this for barn 1, stable 7

1 | 7 | 2009-12-09 10:00:00 | empty
1 | 7 | 2009-12-10 10:30:00 | show horse

If I run the SELECT query above, I get the following row returned for barn 1, stable 7:

1 | 7 | 2009-12-10 10:30:00 | empty

it gets the right maximum timestamp, just the wrong stable_contents.

Any ideas?

Answer 1

You need to use a subquery:

SELECT barn_id, stable_number, timestamp, stable_contents
FROM stable
WHERE (barn_id, stable_number, timestamp) IN 
(SELECT barn_id, stable_number, max(timestamp) as timestamp
FROM stable
GROUP BY barn_id, stable_number)

Unless you tell it, the database has no way to know that you want the stable contents from the row with the highest timestamp. You could have a query with several aggregation clauses max(timestamp), min(timestamp) etc.

Answer 2

SELECT s.*
FROM (
    SELECT barn_id, stable_number, max(timestamp) as timestamp
    FROM stable
    GROUP BY barn_id, stable_number) d
INNER JOIN stable s ON s.barn_id = d.barn_id
                       AND s.stable_number = d.stable_number
                       AND s.timestamp = d.timestamp

And it's usually a better way to have two tables: one contains current state, another contains historical data.

Answer 3

It really should give you an error instead of returning undefined data because you're trying to get non-aggregated data that isn't in your GROUP BY (stable_contents). I would use the following query, which finds all rows for a stable where there isn't a row after it for the same stable:

SELECT
     T1.barn_id,
     T1.stable_number,
     T1.timestamp,
     T1.stable_contents
FROM
     Stable AS T1
LEFT OUTER JOIN Stable AS T2 ON
     T2.barn_id = T1.barn_id AND
     T2.stable_number = T1.stable_number AND
     T2.timestamp > T1.timestamp
WHERE
     T2.barn_id IS NULL     -- The only way for this to be NULL is if no match was found

Alternatively:

SELECT
     T1.barn_id,
     T1.stable_number,
     T1.timestamp,
     T1.stable_contents
FROM
     Stable AS T1
WHERE
     NOT EXISTS
     (
          SELECT
               *
          FROM
               Stable AS T2
          WHERE
               T2.barn_id = T1.barn_id AND
               T2.stable_number = T1.stable_number AND
               T2.timestamp > T1.timestamp
     )

Answer 4

SELECT  s.*
FROM    barn b
JOIN    stable s
ON      stable_id = 
        (
        SELECT  stable_id
        FROM    stable si
        WHERE   si.barn_id = b.id
        ORDER BY
                barn_id DESC, timestamp DESC, stable_id DESC
        )

Make sure you have a composite index on stable (barn_id, timestamp, stable_id) for this to work fast.

See this article in my blog for more details:

• MySQL: Selecting records holding group-wise maximum (resolving ties)