CODEWITHSUNDEEP
phpmysqlsorting
dsol828
dsol828

Reputation: 413

Unique sorting scenario

I have a mysql database with player statistics.

playerid | player | points | league

I would like to sort a query on points DESC, but some players have played in other leagues, so they have multiple records in the DB.

Ideally, I would like to sort on points in "Major League", and when a player has played in other leagues, list those below. It should look something like this:

John Smith | 27 | Major League
Gary White | 24 | Major League
           | 32 | Minors
Joe Flatts | 15 | Major League

ETC...

I'm not sure if it's easier to create this in a MySQL view or server-side in my PHP.

Anyone have any suggestions?

Upvotes: 0

Views: 75

Answers (1)

Jeffrey Theobald
Jeffrey Theobald

Reputation: 2617

You can do it in SQL by joining up to second copy of the players table (filtering only the major league), but it's not great for larger sizes of tables:

SELECT l.*, (l.league = 'Major League') AS major
FROM league_points AS l 
    LEFT JOIN league_points AS major 
        ON l.player = major.player AND major.league = 'Major League'
ORDER BY major.points DESC, major DESC, league DESC

You probably want to remove the major column from your output, it's just there to ensure that the major league comes first in the ORDER BY operation. If you don't want players who haven't played in the major league to turn up at all, change the LEFT JOIN to just an INNER JOIN.

Upvotes: 1

Related Questions