Join problem

[Wondermoon]

MYSQL: 3.23.36 running on LINUX

I've got two tables:

Table: Titles
Fields: ID, title, index

Table: Values
Fields: ID_title, value, hour


The first table has the unique list of Titles, the second table has
the values each title has on a specific hour. There are several
records on the second table matching the first table. How can I select
the LAST value for each title on the first table, that has
index="abc"? (LAST means with the bigger hour)

I've tried:
-> SELECT Titles.ID, Titles.title, Values.value, Values.hour from
Titles, Values where Values.ID_title=Titles.ID and Titles.index='abc'
group by Titles.ID
It returns the firsts values for each title found on the second table.

-> SELECT Titles.ID, Titles.title, Values.value, Values.hour from
Titles, Values where Values.ID_title=Titles.ID and Titles.index='abc'
group by Titles.ID order by Values.hour
It returns the same values that the one above but ordered by hour
DESC.

-> SELECT Titles.ID, Titles.title, Values.value, max(Values.hour) from
Titles, Values where Values.ID_title=Titles.ID and Titles.index='abc'
group by Titles.ID order by Values.hour
It returns the hour's that I want, but the values are still the first
ones and not the ones related to the hour returned...

-> SELECT Titles.ID, Titles.title, Values.value, Values.hour from
Titles, Values where Values.ID_title=Titles.ID and Titles.index='abc'
It returns all the values with repective hour for all the titles. (If
I could just filter theese results to get just the last value for each
Title)

Any ideas on how to do this??
I'm starting to crash my head on the wall on this...

PS: Sorry about the bad english

 

[OldProgrammer]

If I understand you correctly you simply need to add the function MAX()to your query:

Select t.id, t.title, max(v.value), max(v.hour)
from titles t, values v
where v.id_title=t.id
and t.index='abc'
group by t.id, title

Hope this helps?

 

[Guest_imported]

I've solved in a difrent way... I've selected all thje records as in the 3rd option that I gave, into an array, and I've filtered afterwards...

The solution you gave will return me the maximum value that exists... what I want is the value corresponding to the maximum hour...

It's not possible to do it in a simple query, with MySQL in this version...

http://www.mysql.com/doc/en/example-Maximum-column-group-row.html

Tnks anyway...