Need sql help

ordering results when using IN

[figment88]

ok, not strictly a php question but ...

I'm constructing a SQL statements along lines of

$sql='SELECT * from table where product_ID IN (28,342,56,231,45)';

I want the results to be returned in the same order that they are listed in the IN clause, e.g.

the product with productID 28
the product with productID 342
the product with productID 56
etc.

I know how to post-process the results in PHP to achieve this result, but I thought it would be more efficient to do it all in SQL. Does anyone have an idea how to construct the ORDER BY clause?

[arran]

Hi figment,

You could use a temporary table. Something like:

CREATE TEMPORARY TABLE temp_order (id int, the_order int)

INSERT INTO temp_order VALUES (28,1),(342,2),(56,3),(231,4),(45,5)

SELECT * FROM table, temp_order WHERE table.product_ID = temp_order.id ORDER BY temp_order.the_order

DROP TABLE temp_order

arran.

[figment88]

arran,

Thanksfor the response. The logic looks like it should work. I've never used temporary tables before, so I was wondering if you could give me a little more info.

Since each site visitor will have a different set of productID's, would there be a seperate temporary table for each visitor?

Is creating/deleting temporary tables very resource intensive?

thanks again.

[SeanW]

SELECT * from table where product_ID IN (28,342,56,231,45) ORDER BY product_ID IN (28,342,56,231,45)

Sean

[arran]

Sean,

This didn't work for me (returned rows in ascending product_ID order).

SELECT * from table where product_ID IN (28,342,56,231,45) ORDER BY product_ID IN (28,342,56,231,45)

[arran]

figment,

Since each site visitor will have a different set of productID's, would there be a seperate temporary table for each visitor?

Yes - a new temporary table per connection.

Is creating/deleting temporary tables very resource intensive?

It will be noticeably slower than a single query (due to the multiple database calls) - try doing a quick test to verify the slowdown is acceptable.

Unfortunately, i can't see how you could do it without the temporary table.

[figment88]

I also wasn't able to get sean's approach to work, but I did a modification such as

SELECT *
FROM table
WHERE product_ID
IN ( 400, 28, 388, 241, 277 )
ORDER BY FIND_IN_SET( product_ID, "400,28,388,241,277" )

This seems to work with only one database call.

Thanks everyone for your help.

[SeanW]

FIND_IN_SET... interesting. I saw the ORDER BY col IN(...) in the mysql docs somewhere, maybe it's a newer feature, never tried it myself.

Glad to hear you got it going, going to have to remember this one.

Sean

[coopster]

IN

is a comparison function/operator that results in TRUE/FALSE so you won't be able to use it in your ORDER BY and get the results you expect. Ideally, a UNION ALL would be great here, but if you can't use a UNION you can use a modified ORDER BY clause. Here are some examples:

UNION:
[webmasterworld.com...]

ORDER BY:
[webmasterworld.com...]
[webmasterworld.com...]

[SeanW]

Coopster, I dug up the reference to using IN in the ORDER BY clause

[dev.mysql.com...]

Take a look at the user contributed stuff at the bottom. Upon closer inspection, he uses it to bring selected items to the top of the list, but still sorted.

Sean

[coopster]

Yes, you can use it, but you notice that the example specified had to list the sorting field twice in ORDER to get the sort order as desired, the first order put the TRUE statements first, the second sorted the remainders by ASCending order.

However, in order to get a mixed list like the example in this thread to come out in order you would still need to use a separator of sorts as described in the links I left. The IN operator is not going to work.

That may not be so clear so let me demonstrate using the same mini piece of data used in the MySQL doc page you mentioned.

-- 
-- Create the table: 
-- 
DROP TABLE IF EXISTS countries; 
CREATE TABLE countries ( 
  iso_code CHAR(2), 
  name CHAR(20) 
); 
-- 
-- Add some test data: 
-- 
INSERT INTO countries VALUES ('AS', 'American Samoa'); 
INSERT INTO countries VALUES ('DZ', 'Algeria'); 
INSERT INTO countries VALUES ('AL', 'Albania'); 
INSERT INTO countries VALUES ('AF', 'Afghanistan'); 
INSERT INTO countries VALUES ('US', 'United States'); 
INSERT INTO countries VALUES ('UK', 'United Kingdom'); 
-- 
-- UNION works nice (and fast!): 
-- 
SELECT * FROM countries WHERE iso_code = 'UK' 
UNION ALL 
SELECT * FROM countries WHERE iso_code = 'US' 
UNION ALL 
SELECT * FROM countries WHERE iso_code = 'AF' 
UNION ALL  
SELECT * FROM countries WHERE iso_code = 'AL' 
UNION ALL 
SELECT * FROM countries WHERE iso_code = 'DZ' 
UNION ALL 
SELECT * FROM countries WHERE iso_code = 'AS' 
; 
-- 
-- ORDER BY with DESC keyword applied: 
-- 
SELECT * FROM countries WHERE iso_code IN ('UK', 'US', 'AF', 'AL', 'DZ', 'AS') ORDER BY  
 iso_code = 'UK' DESC, 
 iso_code = 'US' DESC, 
 iso_code = 'AF' DESC, 
 iso_code = 'AL' DESC, 
 iso_code = 'DZ' DESC, 
 iso_code = 'AS' DESC 
; 
-- 
-- Both of the queries return the same exact result set: 
-- 
+----------+----------------+ 
¦ iso_code ¦ name           ¦ 
+----------+----------------+ 
¦ UK       ¦ United Kingdom ¦ 
¦ US       ¦ United States  ¦ 
¦ AF       ¦ Afghanistan    ¦ 
¦ AL       ¦ Albania        ¦ 
¦ DZ       ¦ Algeria        ¦ 
¦ AS       ¦ American Samoa ¦ 
+----------+----------------+ 
-- 
-- ORDER BY with IN operator won't work: 
-- 
SELECT * FROM countries WHERE iso_code IN ('UK', 'US', 'AF', 'AL', 'DZ', 'AS') ORDER BY  
 iso_code IN ('UK', 'US', 'AF', 'AL', 'DZ', 'AS') DESC 
; 
+----------+----------------+ 
¦ iso_code ¦ name           ¦ 
+----------+----------------+ 
¦ AS       ¦ American Samoa ¦ 
¦ DZ       ¦ Algeria        ¦ 
¦ AL       ¦ Albania        ¦ 
¦ AF       ¦ Afghanistan    ¦ 
¦ US       ¦ United States  ¦ 
¦ UK       ¦ United Kingdom ¦ 
+----------+----------------+ 
-- 
-- ORDER BY with IN operator won't work, 
-- not even if we add the iso_code column again: 
-- 
SELECT * FROM countries WHERE iso_code IN ('UK', 'US', 'AF', 'AL', 'DZ', 'AS') ORDER BY  
 iso_code IN ('UK', 'US', 'AF', 'AL', 'DZ', 'AS') DESC, iso_code 
; 
+----------+----------------+ 
¦ iso_code ¦ name           ¦ 
+----------+----------------+ 
¦ AF       ¦ Afghanistan    ¦ 
¦ AL       ¦ Albania        ¦ 
¦ AS       ¦ American Samoa ¦ 
¦ DZ       ¦ Algeria        ¦ 
¦ UK       ¦ United Kingdom ¦ 
¦ US       ¦ United States  ¦ 
+----------+----------------+

[SeanW]

Got it, thanks for the clarification.

Sean