SELECT DISTINCT or LIMIT 1 Question

Probably a simple one for SQL guru's.

I have two tables. First one has a unique ID that records one event.

|event_ID|event_name|other_data|
1 | event a | something else
2 | event b | something else

In the other table I have comments on the event.

|comment_ID | event_ID | comment | other_Data|
100 | 1 | comment 1 | something else
101 | 1 | comment 2 | something else again
102 | 2 | comment 1 | something else

comment_ID is unique on that one, but the same event_ID can have multiple comments.

When I list all events in an overview table, I want to also show the latest comment. And the latest only.

A SELECT on the two joined tables gives me all the comments and event data duplicated as many times as there are comments. Even a DISTINCT one. LIMIT 1 gives me only one event. I'm on MySQL 3.23 so can't do subqueries.

Is there any way to something like

SELECT e.events_name, c.comment (LIMIT 1) FROM events e LEFT JOIN comments c on e.events_ID = c.events_ID // so LIMIT 1 only affects that column?

The result I want to be:

row1 -> event a, comment 2
row2 -> event b, comment 1

Instead of:

row1 -> event a, comment 1
row2 -> event a, comment 2
row3 -> event b, comment 1

 

You could do an "order by comment_id desc", then only output one event comment, it should have the latest comment first. You could then probably also use limit 1 which would give you what you want.

 

Your first option would give him lots of data that he does not need, and your secon option would only output the last comment of all comments, and not per event.



I'd like to do it using a sub query. But since you can't use sub queries you may have to do two queries. Something like this:

//get the latest comment per event_id
SELECT MAX(comment_id) FROM comments
GROUP BY event_id

//then get the comments
SELECT * FROM comments
WHERE comment_ID IN(...put results from last query in a comma separated string here...)

Don't know if you can use IN in mysql 3.23, but you could always rewrite the sql to something like
SELECT * FROM comments where comment_ID=x OR comment_ID=y OR comment_ID=z ...and so on for each result of the first query.

 

I considered the first option but it's not very nice to duplicate all that data so indeed I want to avoid that.

Looks like I can't do it in one simple query then... That'll be one more tick in the box for RHEL4 with native MySQL 4 and package support.

 

Even though it's not the most elegant way, you don't necessarilly have to display all the data from the query on your page. Are you using php?

 

Yes, PHP and yes, I can skip those rows I don't want. But I was hoping MySQL could have done the trick.

 

Yeah, I know. But it's easier said than done. I can stick with Red Hat Enterprise 3 and upgrade MySQL to 4+ but then it's not included in the automatic updates and patches etc. which is a headache I can do without.

I'll do it the cumbersome way for the time being... Thanks all for chiming in.

 

I consulted the engineers a while back and they are going to charge me big time or I can frig about myself which I won't do because quite frankly, the command line and all that isn't really a place I enjoy hanging out at. Webmin is cool but I won't do any mission critical stuff in the black window in PuTTy unless I get my own Red Hat certs or hire someone in-house.

The guy said it requires recompiling PHP to link against the new libraries... RHEL3's up2date repositories don't support MySQL > 3, when the lot goes down and they have to re-build it, it adds to the recovery time when custom installations are done. All that, and the fact Red Hat doesn't support MySQL 5 (I thought might as well jump up right away though then I probably also have to test all my PHP against a newer PHP version) made me decide against it.

If you say you can run MySQL's own RPMs instead of Red Hats then that could be an option but I'd still have to organise some sort of migration server or a rekick and then data(base) conversion / migration... Just thinking of it makes me feel fine as is I'm just not a sys admin geek like you I guess.

But if I keep hitting these little things, that upgrade day will come close faster and faster.