Problem with Sort Order in MySQL 1

[rezzz]

I'm having a problem where I want a result set to come out in the order of the IN.

SELECT userid,username
FROM user
WHERE userid IN (3,2,4)

and regardless of the ORDER BY or GROUP BY that I do, for some reason the resultset will always be 2,3,4. I'd like to get it in the order that it appears in the IN clause. Is this at all possilbe? Thanks in advanced

 

[dalchri]

I would actually join onto the table that you got the foreign key values out of if that is where you got the values out of the In clause. For example:

SELECT userid, username
FROM user 
INNER JOIN customer 
USING (userid)
WHERE customername Like '%ed%'
ORDER BY customer.customername

If there is no table that contains the correct sort field, then you'll need to create a temporary table and join onto that:

CREATE TEMPORARY TABLE sortorder (userid INT, sortorder INT);

INSERT INTO sortorder (userid, sortorder) VALUES (3, 1);
INSERT INTO sortorder (userid, sortorder) VALUES (2, 2);
INSERT INTO sortorder (userid, sortorder) VALUES (4, 3);

SELECT userid, username
FROM user 
INNER JOIN sortorder 
USING (userid)
ORDER BY sortorder.sortorder;

 

[r937]

mysql has this absolutely gorgeous function called FIELD which can be used for exactly this purpose:

SELECT userid,username
FROM user
WHERE userid IN (3,2,4)
ORDER BY FIELD(userid,3,2,4)

sweet, eh?



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[rezzz]

THANK YOU SO VERY MUCH!!! -- You have no idea how long I've spent on this -- That FIELD function works great!!

Thanks again to everyone!!