CODEWITHSUNDEEP
mysql
user1716672
user1716672

Reputation: 1073

Mysql - order by certain strings

I have a players table. Each player has a position which is either goalkeeper, defender, midfielder or forward. I want to return all players, with forwards first, then midfielders, then defenders, then goalkeepers. Obviously, if I do:

SELECT * 
FROM  'Player' 
ORDER BY position

the defenders will be returned first....

Upvotes: 3

Views: 2529

Answers (4)

JustinDanielson
JustinDanielson

Reputation: 3185

You could use a CASE statement in the ORDER BY clause.

SELECT *
FROM Player 
ORDER BY CASE Position WHEN "forward" THEN 1
                       WHEN "defender" THEN 2
                       WHEN "midfielder" THEN 3
                       WHEN "goalkeeper" THEN 4 END ASC

SQL Fiddle Example

Upvotes: 1

eggyal
eggyal

Reputation: 125835

Either—

  • Use FIELD() as suggested by @Bohemian, however note that this will not scale well with larger tables (as a filesort must be performed on the calculated function result for each record).

  • Make position an ENUM:

    ALTER TABLE Player
  MODIFY position ENUM('forward','midfielder','defender','goalkeeper');

    Then the results of your existing query will be sorted accordingly:

    SELECT * FROM Player ORDER BY position;

    This is because, as documented under The ENUM Type:

     Sorting

    ENUM values are sorted based on their index numbers, which depend on the order in which the enumeration members were listed in the column specification.

  • Make a table to hold the position ordering:

    CREATE TABLE position_order (
  position VARCHAR(15) PRIMARY KEY,
  priority TINYINT UNSIGNED
);

INSERT INTO position_order
  (position, priority)
VALUES
  ('forward'   , 1),
  ('midfielder', 2),
  ('defender'  , 3),
  ('goalkeeper', 4)
;

ALTER TABLE Player
  ADD FOREIGN KEY (position) REFERENCES position_order (position);

    Then the results of your query can be sorted by joining the tables and sorting on the new priority column:

    SELECT * FROM Player JOIN position_order USING (position) ORDER BY priority;

Note that you may wish to read Chris Komlenic's article 8 Reasons Why MySQL's ENUM Data Type Is Evil before deciding which approach to take.

Upvotes: 3

Bohemian
Bohemian

Reputation: 424983

Use FIELD():

SELECT * 
FROM Player 
ORDER BY FIELD(position,'forward', 'midfielder', 'defender', 'goalkeeper')

Upvotes: 0

juergen d
juergen d

Reputation: 204746

Use FIND_IN_SET()

SELECT * 
FROM Player 
ORDER BY find_in_set(position,'forward,midfielder,defender,goalkeeper')

Upvotes: 5

Related Questions