CODEWITHSUNDEEP
mysqlsql
Pshtiwan KarDo
Pshtiwan KarDo

Reputation: 87

How to return records in MySQL with "custom" ordering

I'm trying to return records based on their IDs in MySQL without ordering.

But when I run the query it will order them from the lowest ID number to highest one.

SELECT * FROM events WHERE id=11 or id=4 or id=9 or id=5

The result will like these: 4,5,9,11

How can return like this : 11,4,9,5

Upvotes: 1

Views: 948

Answers (3)

Raymond Nijland
Raymond Nijland

Reputation: 11602

I believe you want to use FIELD()

SELECT * FROM events WHERE id=11 or id=4 or id=9 or id=5
ORDER BY FIELD(id, 11,4,8,5)

Or the more ANSI SQL method (works also for other databases vendors)

SELECT 
 events.*
FROM (
  SELECT 
     11 AS id
   , 1 AS position
  UNION ALL
  SELECT
     4 AS id
   , 2 AS position
  UNION ALL
  SELECT
     8 AS id
   , 3 AS position 
  UNION ALL
  SELECT 
     5 AS id
   , 4 AS position 
) AS sorting
INNER JOIN 
 events 
ON
 sorting.id = events.id
ORDER BY 
 sorting.position ASC

Or the better ANSI SQL like it should (works also for other databases vendors)

SELECT * FROM events WHERE id=11 or id=4 or id=9 or id=5
ORDER BY CASE WHEN id = 11 THEN 1
              WHEN id = 4 THEN 2
              WHEN id = 8 THEN 3
              WHEN id = 5 THEN 4
              ELSE 5
         END

Upvotes: 6

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

Try using ORDER BY FIELD (id, ...):

SELECT *
FROM events
WHERE id IN (4, 5, 9, 11)
ORDER BY FIELD (id, 11, 4, 9, 5)

Demo

As to why your current query is showing the 4,5,9,11 order, even without your using an explicit ORDER BY clause, one explanation is that the id column is the clustered primary key for your table. In that case, the data would actually be stored in this order on disk, and when selecting, this would be the natural order returned.

Edit:

On other database vendors, which don't support FIELD, we can order using a CASE expression:

SELECT *
FROM events
WHERE id IN (4, 5, 9, 11)
ORDER BY
    CASE WHEN id = 11 THEN 1
         WHEN id = 4  THEN 2
         WHEN id = 9  THEN 3
         WHEN id = 5  THEN 4
         ELSE 5 END;

Upvotes: 6

Tarreq
Tarreq

Reputation: 1365

** Updateded As you want fixed order, you can use ORDER BY FIELD :

SELECT * FROM events
WHERE id IN (4, 5, 9, 11)
ORDER BY FIELD (id, 11, 4, 9, 5)

Upvotes: 2

Related Questions