mySQL - predefined order

recently handgestrickt had a problem. we wanted to sort some database-content in our own predefined order. this is not directly possible with mySQL. but there is a good trick, that does this work:

SELECT name FROM names ORDER BY FIND_IN_SET(names_id,"1,13,5,7,8");

this is pretty tricky, huh? but it does not really make sense here, because you could also read out your wished order row by row. it starts to make sense, when you want to do multiple sorting and additonally set LIMIT. because if you would do this inside a scripting language row by row, things become complicated. for example:

SELECT
        A.name,
        B.GROUP
FROM
        names AS A,
        groups AS B
WHERE
        A.groups_id = B.groups_id
ORDER BY
        FIND_IN_SET(B.groups_id,"5,6,2,3,1,4"),
        A.name ASC
LIMIT 0,20

give it a try! it works fantastic. and no need to read all data into a scripting language and then order and limit!

Re: mySQL - predefined order

Reply

Re: mySQL - predefined order

Reply

Re: mySQL - predefined order

Reply