Jump to content


Photo

Join only the HIGHEST value from a table


  • Please log in to reply
6 replies to this topic

#1 mlat

mlat
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 10 August 2006 - 02:55 PM

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?

#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 10 August 2006 - 03:00 PM

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?


try something like this:

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

You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 mlat

mlat
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 10 August 2006 - 03:05 PM

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

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 August 2006 - 03:10 PM

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

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 )

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 mlat

mlat
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 14 August 2006 - 05:21 AM

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

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 14 August 2006 - 05:25 AM

I'll take another look... actually what I posted earlier won't work for other reasons.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 mlat

mlat
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 14 August 2006 - 05:51 AM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users