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
Post a Comment