Geek Answers Handbook

ORDER BY with CASE MySQL

I have a “points” query that shows statistics for the league in which I participate. I am trying to order a request based on several conditions.

  • If the team took 1-25 points, the order in wins, points
  • If the team took a place not at the top of 25 (from 26 to 42 in points), order by points.

The best way I could describe this is that it should act as two tables. Teams that won first place (1-25) should be sorted together, and teams with a rating (26-42) should be sorted together.

Here is my request.

SELECT m.Team AS team
, SUM( r.points ) AS points
, SUM(CASE
    WHEN rank = 1 THEN 1
    ELSE 0
  END) AS Wins
, SUM(CASE
    WHEN rank < 6 THEN 1
    ELSE 0
  END) AS T5
, SUM(CASE
    WHEN rank < 11 THEN 1
    ELSE 0
  END) AS T10
, SUM(CASE
    WHEN rank < 21 THEN 1
    ELSE 0
  END) AS T20
, ROUND(Avg(r.points),2) AS ppr
, ROUND(Avg(r.rank),2) as avg_finish
, MIN(r.rank) as best
FROM members m
LEFT JOIN results r ON r.team_id = m.M_ID
GROUP BY team
ORDER BY 
    CASE
        WHEN COUNT(*) < 26
        THEN Wins 
        WHEN COUNT(*) > 25
        THEN points
    END DESC, points DESC;

I get the error message “Victory link” is not supported (group function link) due to my nickname “Winning.” Instead I tried:

ORDER BY 
    CASE
        WHEN COUNT(*) < 26
        THEN SUM(CASE
            WHEN rank = 1 THEN 1
            ELSE 0
        END)  
        WHEN COUNT(*) > 25
        THEN points
    END DESC, points DESC;

, COUNT (*) > 25 CASE , . , ORDER BY?

+4
5

ORDER BY , Wins ORDER.

SELECT * FROM (
SELECT m.Team AS team
, SUM( r.points ) AS points
, SUM(CASE
    WHEN rank = 1 THEN 1
    ELSE 0
  END) AS Wins
, SUM(CASE
    WHEN rank < 6 THEN 1
    ELSE 0
  END) AS T5
, SUM(CASE
    WHEN rank < 11 THEN 1
    ELSE 0
  END) AS T10
, SUM(CASE
    WHEN rank < 21 THEN 1
    ELSE 0
  END) AS T20
, ROUND(Avg(r.points),2) AS ppr
, ROUND(Avg(r.rank),2) as avg_finish
, MIN(r.rank) as best
FROM members m
LEFT JOIN results r ON r.team_id = m.M_ID
GROUP BY team ) XXX
ORDER BY 
    CASE
        WHEN COUNT(*) < 26
        THEN Wins 
        WHEN COUNT(*) > 25
        THEN points
    END DESC, points DESC;
+1

Wins . MySQL order by, .

:

ORDER BY (CASE WHEN COUNT(*) < 26 THEN 1 ELSE 2 END),  -- put the top ranking first,
         (CASE WHEN COUNT(*) < 26 THEN wins END) DESC,
         points DESC

MySQL select:

SELECT m.Team AS team, SUM( r.points ) AS points,
       SUM(rank = 1) then Wins,
       SUM(rank < 6) as T5,
       SUM(rank < 11) as T10,
       SUM(rank < 21) as T20
       . . .

EDIT:

, order by :

ORDER BY (points <= 26) DESC,  -- put the top ranking first,
         wins desc,
         points DESC
0

, MySQL , ROW_NUMBER() RANK(). , ROW_NUMBER() " 25" . ROW_NUMBER().

, . , .

, "-25" . , , "-25" .

, ... " " (a) "-25" (t) :

 SELECT a.*
   FROM ( 
          SELECT m.team                   AS team
               , SUM( r.points )          AS points
               , SUM(IF( r.rank=1  ,1,0)) AS wins
               , SUM(IF( r.rank<6  ,1,0)) AS t5
               , SUM(IF( r.rank<11 ,1,0)) AS t10
               , SUM(IF( r.rank<21 ,1,0)) AS t20
               , ROUND(AVG( r.points ),2) AS ppr
               , ROUND(AVG( r.rank   ),2) AS avg_finish
               , MIN( r.rank )            AS best
            FROM members m
            LEFT
            JOIN results r
              ON r.team_id = m.m_id
           GROUP BY m.team
        ) a
   LEFT
   JOIN ( SELECT tm.team                   AS team    
               , SUM(IF( tr.rank=1  ,1,0)) AS wins
            FROM members tm
            LEFT
            JOIN results tr
              ON tr.team_id = tm.m_id
           GROUP BY tm.team
           ORDER
              BY SUM( tr.points ) DESC
               , SUM(IF( tr.rank=1  ,1,0)) DESC
               , tr.team_id DESC
           LIMIT 25
        ) t
     ON t.team = ta.team
  ORDER
     BY t.wins   DESC
      , a.points DESC
      , a.team   DESC

"a" - , .

"t" , ORDER BY LIMIT, 25 . ORDER BY LIMIT, 25 , . ( ORDER BY, ... , 25- 26- , ...

, a " 25", . ( , , t, NULL t... i.e. t.wins NULL, ORDER BY .

wins t. , , t, NULL.

  ORDER
     BY IF(t.team IS NULL,NULL,a.wins) DESC
      , a.points DESC
      , a.team   DESC
0

, , points. ,

ORDER BY (rank > 25) ASC
, CASE WHEN rank <= 25 THEN Wins ELSE 0 END
, points

MySQL - AUTO_INCREMENT.

DROP TEMPORARY TABLE IF EXISTS tmp_teams;
CREATE TEMPORARY TABLE tmp_teams (
    rank INT AUTO_INCREMENT PRIMARY KEY,
    team varchar(255),
    points INT,
    Wins INT,
    T5 INT,
    T10 INT,
    T20 INT,
    ppr INT,
    avg_finish INT,
    best INT
) AS
    SELECT null AS rank
    , m.Team AS team
    , SUM( r.points ) AS points
    , SUM(CASE
        WHEN rank = 1 THEN 1
        ELSE 0
      END) AS Wins
    , SUM(CASE
        WHEN rank < 6 THEN 1
        ELSE 0
      END) AS T5
    , SUM(CASE
        WHEN rank < 11 THEN 1
        ELSE 0
      END) AS T10
    , SUM(CASE
        WHEN rank < 21 THEN 1
        ELSE 0
      END) AS T20
    , ROUND(Avg(r.points),2) AS ppr
    , ROUND(Avg(r.rank),2) as avg_finish
    , MIN(r.rank) as best
    FROM members m
    LEFT JOIN results r ON r.team_id = m.M_ID
    GROUP BY team
    ORDER BY points DESC, Wins DESC, r.team_id ASC -- define the order for rank
;

SELECT *
FROM tmp_teams
ORDER BY (rank > 25) ASC
, CASE WHEN rank <= 25 THEN Wins ELSE 0 END
, points;

(@rank), :

SELECT *
FROM (
    SELECT m.Team AS team
    , SUM( r.points ) AS points
    , SUM(CASE
        WHEN rank = 1 THEN 1
        ELSE 0
      END) AS Wins
    , SUM(CASE
        WHEN rank < 6 THEN 1
        ELSE 0
      END) AS T5
    , SUM(CASE
        WHEN rank < 11 THEN 1
        ELSE 0
      END) AS T10
    , SUM(CASE
        WHEN rank < 21 THEN 1
        ELSE 0
      END) AS T20
    , ROUND(Avg(r.points),2) AS ppr
    , ROUND(Avg(r.rank),2) as avg_finish
    , MIN(r.rank) as best
    , @rank := @rank + 1 AS rank
    FROM members m
    LEFT JOIN results r ON r.team_id = m.M_ID
    CROSS JOIN (select @rank := 0) AS init_rank
    GROUP BY team
    ORDER BY points DESC, Wins DESC, r.team_id ASC -- define the order for rank
    LIMIT 1000000000 -- workaround for some versions/settings to force sorting a subquery result
) sub
ORDER BY (rank > 25) ASC
, CASE WHEN rank <= 25 THEN Wins ELSE 0 END
, points
0

- . , . , , : 

SET @rownum = 0; 

SELECT *, (@rownum := @rownum + 1) AS chase FROM (
SELECT m.Team AS team
, SUM( r.points ) AS points
, SUM(r.rank = 1) AS Wins
, SUM(r.rank < 6) AS T5
, SUM(r.rank < 11) AS T10
, SUM(r.rank < 21) AS T20
, ROUND(Avg(r.points),2) AS ppr
, ROUND(Avg(r.rank),2) as avg_finish
, MIN(r.rank) as best
FROM members m
LEFT JOIN results r ON r.team_id = m.M_ID
GROUP BY team
ORDER BY points DESC ) AS ptotal
ORDER BY CASE
    WHEN chase <= 24
    THEN Wins
END DESC, points DESC;

. , . , , !

0

More articles:


All Articles