[da.bass]

hi,
i'm having a serious sql problem:

My table layout looks like this (simplified):
content: name, version, data / primary key is (name,version)
theme: contentname, name

The only thing I have is theme.name.
What I want is all the data inside content that corresponds to theme.name and has the maximum version.

Example:
I have name = 'test'. This gives me the contentnames 'content1' and 'content2'.
Now I want the data of 'content1' with the maximum version (which e.g. is 5) and the data of 'content2' with its maximum version (which e.g. is 24).

To do this in more than one query is no problem, but I want to do this in one single query. I tried multiple possibilites using 'group by', 'havin', subselects... but still can't get a working query.

hope this problem could be solved soon
thx

[NeverMind]

I am not quite sure what you mean since you didn't specify in which table the name = 'test'..
anyway, check out the mysql function MAX() and ORDER BY DESC ..

[da.bass]

OK, I found one solution, but I'm not sure if it will work in all cases:

Code:

SELECT name as a, data, version FROM content
WHERE name IN (
  SELECT contentname FROM theme
  WHERE name = 'test'
)
AND version = (
  SELECT max(version)
  FROM stylesheets
  WHERE name = a
)