select - MySQL order by the highest score -


i have 2 tables:

  create table `fl_poll` (   `id_poll` int(11) not null auto_increment,   `id_player` int(11) not null,   `position` varchar(50) not null,   `score` int(11) not null,   `time` timestamp not null default current_timestamp on update current_timestamp,   `ip` varchar(100) not null,   primary key (`id_anketa`) ) engine=myisam;  create table `fl_player` (   `id` int(11) not null auto_increment,   `id_team` int(11) not null,   `name` varchar(100) not null,   `lastname` varchar(100) not null,   primary key (`id`) ) engine=myisam; 

and ask, solution how select players table fl_player, count score , order top 3 players have highest score , others order lastname? example have 6 players:

table fl_player:

id     |     lastname     ------------------------ 1             smith            2            johnson           3             todd              4             dragon             5             bond              6             black        

table fl_poll:

+-----------+-------+ | id_player | score | +-----------+-------+ |         1 |     2 | |         2 |     4 | |         3 |     6 | |         4 |     8 | |         5 |    10 | |         6 |    12 | +-----------+-------+ 

and wish result this:

+-----------+----------------------------------------+ | id_player |                lastname                | +-----------+----------------------------------------+ |         6 | black <-- top 3 highest score     | |         5 | bond                                   | |         4 | dragon                                 | |         2 | johnson <-- order lastname | |         1 | smith                                  | |         3 | todd                                   | +-----------+----------------------------------------+ 

  select   fl_poll.id_player, fl_player.lastname, fl_poll.score, 1 type       fl_player       join fl_poll on fl_poll.id_player = fl_player.id      fl_poll.score >= (              select min(score) (                select score fl_poll order score desc limit 3              ) t            )  union    select   fl_poll.id_player, fl_player.lastname, fl_poll.score, 2 type       fl_player       join fl_poll on fl_poll.id_player = fl_player.id      fl_poll.score < (              select min(score) (                select score fl_poll order score desc limit 3              ) t            )  order case when type = 1 score end desc, lastname 

Comments

Popular posts from this blog

How to mention the localhost in android -

php - Calling a template part from a post -