Looking for SELECT statement

[Mick White]

mysql> select * from guestbook;
+----+--------+---------+-----------------+----------------+
| id | fname | lname | comments | time_in |
+----+--------+---------+-----------------+----------------+
| 1 | Mick | White | Test 123 | 20050303191815 |
| 2 | Ann | White | Hello World | 20050303191931 |
| 3 | Ann | White | It's a nice day | 20050303191959 |
| 4 | Seamus | White | The cat | 20050303192028 |
| 5 | Matt | White | Words of wisdom | 20050303192509 |
| 6 | Sharon | Pombert | In the sticks | 20050303221802 |
| 7 | Tony | Jones | Cousin | 20050303222027 |
| 8 | Tony | Jones | Cousin's cousin | 20050303222133 |
| 9 | Matt | Dye | UR | 20050303222133 |
+----+--------+---------+-----------------+----------------+

I am trying to retrieve each person's latest comment. i.e every record
except id's 2 & 7 in this case.
Is this possible?
Mick

[Bill Karwin]

Mick White wrote:

mysql> select * from guestbook;
+----+--------+---------+-----------------+----------------+
| id | fname | lname | comments | time_in |
+----+--------+---------+-----------------+----------------+
| 1 | Mick | White | Test 123 | 20050303191815 |
| 2 | Ann | White | Hello World | 20050303191931 |
| 3 | Ann | White | It's a nice day | 20050303191959 |
| 4 | Seamus | White | The cat | 20050303192028 |
| 5 | Matt | White | Words of wisdom | 20050303192509 |
| 6 | Sharon | Pombert | In the sticks | 20050303221802 |
| 7 | Tony | Jones | Cousin | 20050303222027 |
| 8 | Tony | Jones | Cousin's cousin | 20050303222133 |
| 9 | Matt | Dye | UR | 20050303222133 |
+----+--------+---------+-----------------+----------------+

I am trying to retrieve each person's latest comment. i.e every record
except id's 2 & 7 in this case.
Is this possible?

Yes, it's possible, but it's tricky. SQL doesn't provide a very
seamless solution for problems like this. You're trying to group by one
colums (or set of columns in this case, fname + lname), aggregate by
another column (time_in), and then get the value from a third column
(comments).

Here's one possible solution:

SELECT g1.fname, g1.lname, g1.comments
FROM guestbook AS g1 INNER JOIN
(SELECT gsub.id, MAX(gsub.time_in) FROM guestbook AS gsub
GROUP BY gsub.fname, gsub.lname) AS g2
ON (g1.id = g2.id);

Since this uses subqueries, you must be using at least MySQL 4.1.

Regards,
Bill K.

[Mick White]

Bill Karwin wrote:

Mick White wrote:

mysql> select * from guestbook;
+----+--------+---------+-----------------+----------------+
| id | fname | lname | comments | time_in |
+----+--------+---------+-----------------+----------------+
| 1 | Mick | White | Test 123 | 20050303191815 |
| 2 | Ann | White | Hello World | 20050303191931 |
| 3 | Ann | White | It's a nice day | 20050303191959 |
| 4 | Seamus | White | The cat | 20050303192028 |
| 5 | Matt | White | Words of wisdom | 20050303192509 |
| 6 | Sharon | Pombert | In the sticks | 20050303221802 |
| 7 | Tony | Jones | Cousin | 20050303222027 |
| 8 | Tony | Jones | Cousin's cousin | 20050303222133 |
| 9 | Matt | Dye | UR | 20050303222133 |
+----+--------+---------+-----------------+----------------+

I am trying to retrieve each person's latest comment. i.e every record
except id's 2 & 7 in this case.
Is this possible?

Yes, it's possible, but it's tricky. SQL doesn't provide a very
seamless solution for problems like this. You're trying to group by one
colums (or set of columns in this case, fname + lname), aggregate by
another column (time_in), and then get the value from a third column
(comments).

Here's one possible solution:

SELECT g1.fname, g1.lname, g1.comments
FROM guestbook AS g1 INNER JOIN
(SELECT gsub.id, MAX(gsub.time_in) FROM guestbook AS gsub
GROUP BY gsub.fname, gsub.lname) AS g2
ON (g1.id = g2.id);

Since this uses subqueries, you must be using at least MySQL 4.1.

Thanks, Bill. Unfortunately, I am using 4.0.23. I appreciate your
response. Interesting.
Mick

[Aggro]

Bill Karwin wrote:

SELECT gsub.id, MAX(gsub.time_in) FROM guestbook AS gsub
GROUP BY gsub.fname, gsub.lname

Are you sure this returns the id mathing the max-value in row?

AFAIK, the max value will be indeed the largest value, but the id will
be from undefined row inside the group of matching rows. (In case of
MySQL, I think it will return id from the first row)

Simplified example:
mysql> select * from guestbook;
+------+------+----------------+
| id | name | time_in |
+------+------+----------------+
| 1 | Mick | 20050303191815 |
| 3 | Ann | 20050303191815 |
| 2 | Mick | 20050303191931 |
| 4 | Ann | 20040303191931 |
+------+------+----------------+

mysql> SELECT gsub.id, MAX(gsub.time_in ) FROM guestbook AS gsub
-> GROUP BY name;
+------+--------------------+
| id | MAX(gsub.time_in ) |
+------+--------------------+
| 3 | 20050303191815 |
| 1 | 20050303191931 |
+------+--------------------+

Compare time_in values from first query and second for id 1:
20050303191815 and 20050303191931

As you can see, they don't match, so the returned id is not connected to
the returned maximum value. And if the inner query in your subquery
returns invalid values, the outer query will also return invalid values.
Please correct me if you think the subquery works correctly in this case
or with 4.1, I don't have much experience with those.

[Bill Karwin]

Aggro wrote:

Please correct me if you think the subquery works correctly in this case
or with 4.1, I don't have much experience with those.

No, you're absolutely right! I am very sorry to have suggested the
solution, since it doesn't work.

This points out the trouble with this type of aggregation query in SQL.
When using GROUP BY, the only fields that are safe to reference in the
select-list but not in an aggregation function are columns named in your
GROUP BY statement.

In other words, in this case:

SELECT A, B, MAX(C)
FROM table
GROUP BY A

The value of A is invariant in the grouping, and the value of C is
well-defined as the maximum value of field C in that grouping. But the
value of B is ambiguous.

In some RDBMS implementations, it's actually a semantic error to execute
a query with this ambiguity, and the query fails if you try to do that.

MySQL permits you (wrongly, in my opinion) to execute this type of
query, and it chooses a more or less arbitrary value to return for those
fields. As Aggro points out, it seems to choose the "first" row in the
group (but this is a coincedence of implementation, and not due to any
rule, since there is no such thing as an implicit ordering of rows in a
table).

So for Mick's solution, we can only use fields in the subquery that are
part of the aggregate expression, or are referenced in the GROUP BY
clause. I'm going to make an assumption that the id field is used in
such a way that a greater id value for a given guest is guaranteed to
correspond to a greater value for time_in. That is, MAX(time_in) always
occurs on the same row as MAX(id) for a given guest.

SELECT gsub.fname, gsub.lname, MAX(gsub.id)
FROM guestbook AS gsub
GROUP BY gsub.fname, gsub.lname

This gives us the max comment id per guest, which is also known to be
the most recent entry for that respective guest. Now make a
comma-separated string of all the values of id returned by the above
query, and use it when building the following query (we have to build
the SQL query as a string, because simple parameter substitution with ?
doesn't support lists of values).

SELECT g1.fname, g1.lname, g1.comments
FROM guestbook AS g1
WHERE g1.id IN ( $idlist )

This should work, doesn't violate the grouping-column rule, and doesn't
require subqueries.

Regards,
Bill K.