0

I have a stored procedure that takes in an ordered list of IDs, i.e. ('5,9,1,7,2'). The procedure, to simplify, checks the IDs appear in a table, then outputs these IDs with their place in the order:

CREATE PROCEDURE usp (`idlist` VARCHAR(255))
SELECT 
    @rank := @rank + 1 AS rank
    id
FROM id_table t, (SELECT @rank := 0) s
WHERE FIND_IN_SET(t.id, idlist) > 0

However, MySQL will not order these IDs properly. It scrambles the output to the id order:

+-------+----+
| rank  | id |
+-------+----+
| 1     | 1  |
| 2     | 2  |
| 3     | 5  |
| 4     | 7  |
| 5     | 9  |
+-------+----+

If this were a one-off case, I could simply add:

ORDER BY FIELD(id, '5', '9', '1', '7', '2')

And get the desired output:

+-------+----+
| rank  | id |
+-------+----+
| 1     | 5  |
| 2     | 9  |
| 3     | 1  |
| 4     | 7  |
| 5     | 2  |
+-------+----+

But the list can be any 5 IDs that cannot be "naturally" ordered. So I tried this:

ORDER BY FIELD(id, CONCAT('"', REPLACE(idlist, ',', '","'), '"'));

To make a list of IDs as the order, but that didn't work. How can I get ORDER BY FIELD() to listen to the order in idlist?

(This has some similarity to MYSQL - GROUP_CONCAT AND FIND_IN_SET are mixing values/order? - but the solution there doesn't work for me here, perhaps because of the incrementing @rank, the stored procedure argument, or using FIELD in the actual ORDER BY clause.)

Community
  • 1
  • 1
akoo
  • 3
  • 2
  • `order by find_in_set(...)` will do the trick. mysql will return records in their 'natural' order, unless you tell it how to sort things. – Marc B Dec 22 '14 at 19:38

1 Answers1

1

Just add:

ORDER BY FIND_IN_SET(t.id, idlist)

Don't use field() for this; use find_in_set().

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786