Max in a Group by clause

Databases
1

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.

I will appreciate any pointer you can give me.

Thanks,
Adrien

2 

SELECT class
     , first_name
     , max(age) 
  FROM school 
 GROUP BY class, first_name 
 ORDER BY class, first_name
3

Brilliant, thanks DaveMaxwell!

4

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?

Thanks,
Adrien

5

it gives the correct first_name, though, which is what you asked for

in fact, a close examination of your first post reveals that there are only three columns in your table – first_name, age, and class

so dave’s query is demonstrably correct

however, i know what you’re asking, so let’s try this again :slight_smile:

is this really about classes and students and ages?

could we perhaps see the real table and the real query?

6

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;

Thanks,
Adrien

7 
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

:slight_smile:

8

Works like a charm, thanks r937

9

prego :slight_smile: