Join only the HIGHEST value from a table

[mlat]

Alright lets say I have two tables:

table_alpha
-----------
id


table_beta
-----------
alpha_id
time

Okay, the table beta table has multipule entries for each entry for table alpha. I need to get everything in table alpha, and then join one value from table_beta, which has the largest time value (im storing times in unix format). Anyone know a simple way of doing this?

[obsidian]

[quote author=mlat link=topic=103723.msg413229#msg413229 date=1155221702]
Alright lets say I have two tables:

table_alpha
-----------
id


table_beta
-----------
alpha_id
time

Okay, the table beta table has multipule entries for each entry for table alpha. I need to get everything in table alpha, and then join one value from table_beta, which has the largest time value (im storing times in unix format). Anyone know a simple way of doing this?
[/quote]

try something like this:

[code]
SELECT a.id, MAX(time) AS time FROM table_alpha a, table_beta b WHERE a.id = b.alpha_id GROUP BY a.id;
[/code]

[mlat]

I was only giving a rough example. I need all the values that come with that entry in table_beta in the highest row.

[fenway]

Well, if that's the case, you should use a derived table:

[code]
SELECT a.*, b.*
FROM table_alpha AS a
LEFT JOIN
( SELECT *, MAX( time ) FROM table_beta GROUP BY alpha_id ) AS b ON ( b.alpha_id = a.id )
[/code]

[mlat]

Didn't work, sorry. It returned all beta entries, not one from table for each table alpha entry.

[fenway]

I'll take another look... actually what I posted earlier won't work for other reasons.

[mlat]

It's a rather nasty problem. I've posted this on another board and nobody there knows the solution either. I have a "backup solution" which would make the alpha table hold the second largest time, and just do things based upon that, but it requires a lot of recoding to sync this value. It'd be much better if I just got it working all in one query.