MySQL Query to list records ordered as highest & lowest value alternatively in single query-CodePudding

I've got a table of participant teams who're playing against each other and the table contains the overall previous score of teams. What I want to do is fetch all the team records ordered as the highest score, lowest score, second-highest score, second-lowest score, and so on.

because I want to pair the team with the highest score with the team with the lowest score.

Here's my DB schema,

CREATE TABLE participant_teams (
  team_id int(11) NOT NULL,
  team_name text NOT NULL,
  created_by int(11) NOT NULL,
  team_profile text NOT NULL,
  team_member text NOT NULL,
  baller_event_id_fk int(11) NOT NULL,
  pay_status text NOT NULL COMMENT 'pending, paid, cancelled',
  team_score double NOT NULL,
  registered_on timestamp NOT NULL DEFAULT current_timestamp(),
  updated_on timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


INSERT INTO participant_teams (team_id, team_name, created_by, team_profile, team_member, baller_event_id_fk, pay_status, team_score, registered_on, updated_on) VALUES
(1, 'Summer Havoc', 2, 'uploads/54988c5c833638d4c19546fbc8de1a35.jpg', '5,2', 1, 'paid', 25, '2022-01-04 13:12:39', '2022-01-04 13:12:39'),
(2, 'Jammers Classic', 4, 'uploads/54988c5c833638d4c19546fbc8de1a35.jpg', '4,3', 1, 'paid', 6.25, '2022-01-04 13:13:17', '2022-01-04 13:13:17'),
(3, 'Brickslayers', 7, 'uploads/54988c5c833638d4c19546fbc8de1a35.jpg', '7,10', 1, 'paid', 12.5, '2022-01-05 07:18:08', '2022-01-05 07:18:08'),
(4, 'Basket Bombers', 9, 'uploads/54988c5c833638d4c19546fbc8de1a35.jpg', '9,8', 1, 'paid', 0, '2022-01-05 07:18:45', '2022-01-05 07:18:45');

Right now I've just displayed the teams with scores in descending order as below,

select * from participant_teams order by team_score DESC;

The Records I have in my table looks quite like this,

team_id	team_name	team_score
1	Summer Havoc	25
2	Jammers Classic	6.25
3	Brickslayers	12.5
4	Basket Bombers	0

and the required output is,

team_id	team_name	team_score
1	Summer Havoc	25
4	Basket Bombers	0
3	Brickslayers	12.5
2	Jammers Classic	6.25

Please, Help me out with the query if it's possible.

Note: I am working on Mysql & PHP so any solution in PHP also might work

Thanks.

CodePudding user response：

if your MySQL version support ROW_NUMBER window function you can try to use ROW_NUMBER to do a simple formula to make a group be order by number then use abs function to get absolute value

SELECT team_id,team_name,team_score
FROM (
  SELECT team_id,team_name,team_score,
  abs(ROW_NUMBER() OVER(ORDER BY team_score)  - 
  ROW_NUMBER() OVER(ORDER BY team_score DESC)) rn  
FROM participant_teams
) t1
ORDER BY rn desc,team_score desc

https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=f638b2aa60649f030982bad575463796