Hi,
I have a question with which I’ve been wrestling for some time now.
Let’s imagine I have a table called “school” and that in this table there are three fields: “first_name”, “age” and “class”. Assuming that there can be more students in each class with the same first_name, what query would give me the oldest student for each name in each class?
In other words if there are two Toms and one Jane in class “A”, then the query should return the oldest Tom, Jane and their ages.
Hi DaveMaxwell, I’ve tested the query and it seems to return the correct MAX(age), but not the correct associated name. Do you have any ideas on how to fix this?
Hi 937, no it’s not about schools and you are right the query is indeed correct: I have just tested it against the imaginary table and it works. It does not however wok on the real thing, so let’s ty to be more specific.
I am setting up a DB which contains the translations to the language strings of a website. Each record is made up, amongst other things of the name of the string (name), it’s language (lang), the text itself (text) and the time stamp when this particular record was created. As there are multiple languages the “name” is not unique. Further, as I am keeping all changes in the DB there can be multiple entries with the same “name” and the same “lang”. What will differ is the “text” and the time stamp “mod_time”.
What I need to do is to get the latest “text” for all distinct “name” for a specific “lang”?
I hope this is clearer. Here following is the table structure:
CREATE TABLE locales (
id mediumint(9) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`text` text NOT NULL,
lang varchar(5) NOT NULL,
last_user_id mediumint(9) NOT NULL,
mod_time int(10) unsigned NOT NULL,
PRIMARY KEY (id),
KEY lang (lang),
KEY last_user_id (last_user_id),
KEY mod_time (mod_time),
KEY `name` (`name`),
FULLTEXT KEY `text` (`text`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
SELECT t.name
, t.text
, t.mod_time
FROM ( SELECT name
, MAX(mod_time) AS latest
FROM locales
WHERE lang = 'eng'
GROUP
BY name ) AS m
INNER
JOIN locales AS t
ON t.name = m.name
AND t.mod_time = m.latest