Jump to content

Archived

This topic is now archived and is closed to further replies.

mlat

Join only the HIGHEST value from a table

Recommended Posts

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?

Share this post


Link to post
Share on other sites
[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]

Share this post


Link to post
Share on other sites
I was only giving a rough example. I need all the values that come with that entry in table_beta in the highest row.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
Didn't work, sorry. It returned all beta entries, not one from table for each table alpha entry.

Share this post


Link to post
Share on other sites
I'll take another look... actually what I posted earlier won't work for other reasons.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.