create table user(
  id int not null auto_INCREMENT, 
  name varchar(10), 
  PRIMARY KEY (id)
)ENGINE=innodb;

create table post(
  id int not null auto_INCREMENT,
  user_id int,
  topic varchar(10),
  score int,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES user(id)
)ENGINE=InnoDB;

CREATE INDEX user_score_idx ON post (user_id,score );

select u.id,name,topic,score from (
     select p1.user_id, max(p1.score) max_score 
       from post p1
      group by p1.user_id ) zz
  join post p on zz.max_score=p.score and zz.user_id = p.user_id
  join user u on u.id=p.user_id

   select u.id, u.name, p0.topic, p0.score 
    from user u join post p0
      on p0.id = (select max(id) from post p1 
                   where p1.user_id=u.id
                     and p1.score = (select max(p2.score) 
                                       from post p2 
                                      where p2.user_id=p1.user_id))

    select u.id, u.name, p0.topic, p0.score 
      from user u join post p0
        on p0.id = ( select p1.id 
                       from post p1 
                      where p1.user_id=u.id 
                      order by -p1.score, -p1.id 
                      limit 1)

   select u.id, u.name, p1.topic, p1.score 
    from 
       user u left join post p1 
                     on u.id = p1.user_id
              left join post p2 
                     on p1.user_id=p2.user_id and p1.score < p2.score 
    where p2.id is null

Lonely.K

Alex_Ustinov

create table posts (usr int, dt datetime, post varchar(100));

SELECT usr, SUBSTR(MAX(CONCAT(dt, post)), 1, 19) dt, SUBSTR(MAX(CONCAT(dt, post)), 20) post
FROM posts
GROUP BY usr

 select u.id,name,topic,score from (
     select max(p1.score) max_score 
       from post p1
      group by p1.user_id ) zz
  join post p on zz.max_score=p.score
  join user u on u.id=p.user_id

select u.id, u.name, p.topic, p.score,
  find_in_set(p.id,(
    select group_concat(p2.id order by p2.score desc)
      from post p2 
      where p.user_id = p2.user_id
  )) r
  from user u left join post p
    on u.id = p.user_id
  having (r between 1 and 3) or r is null
  order by u.id, r

select u.id,name,topic,score from (
     select p1.user_id, max(p1.score) max_score 
       from post p1
      group by p1.user_id ) zz
  join post p on zz.max_score=p.score and zz.user_id = p.user_id
  join user u on u.id=p.user_id