CODEWITHSUNDEEP
mysqlsqldatabase
mar
mar

Reputation: 421

SQL query with ORDER BY inside two different groups

Let's say I have a simple table with NBA players:

id,player,mp,ppg (mp - matches played, ppg - points per game).

I need a query that gets all the guys but split the result into two parts:

So desirable output would be (example):

n. player    mp ppg

1. player1   82 32.5
2. player2   56 32.1
3. player3   82 29.7
4. player4   80 27.6

...

70. player70 75 1.5  (lowest in the 30+ games group)
71. player71 29 35.7 (highest in the less than 30 games group)
72. player72 19 31.3

...

Group 1) comes first (is more important) so even if PPG in group 2) is higher than the best one in group 1) it goes down after the worst PPG in the group where players have more than 30 games played.

Is there a way to do that in just one query? I'm using mySQL. Thanks!

Upvotes: 3

Views: 270

Answers (5)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

select player, mp, ppg
from t
order by mp < 30, ppg desc

Upvotes: 0

Prasanna
Prasanna

Reputation: 4703

First order by MP>30 then by PPG

ORDER BY (CASE WHEN mp >= 30 THEN 1 ELSE 2 END) ASC, PPG DESC

Upvotes: 0

paul
paul

Reputation: 22001

select id, player, mp, ppg
from 
    playerTable
order by if (mp>=30, 1, 0) desc, ppg desc, mp desc`

Upvotes: 1

Eugen Rieck
Eugen Rieck

Reputation: 65274

Assuming ppq is allways lower than 1000000, this would work:

SELECT 
  * -- or whatever you want
FROM players
ORDER BY IF(mp>=30,1000000+ppg,mp) desc

This assumes, that the second group is ordered by mp desc (as in example), not by ppg (as in text)

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

SELECT * 
FROM tablename
ORDER BY
    CASE WHEN mp >= 30 THEN PPG END DESC,
    CASE WHEN mp < 30 THEN PPG END ASC

Upvotes: 0

Related Questions