Using Indexes

[berbbrown]

I have a table which has a range of values all with unique id's. The primary key is an auto-increment field.

Imagine: 5 records (1000, 1001, 1002, 1003, 1004)

I have a query:

SELECT * FROM mytable WHERE id IN (1002,1004,1001,1000, 1003)

I want the order of the returned records to be in order in which the id's are specified using the IN keyword.

The result is always returned in primary key order and not the order I specify with IN.

Can anyone help please?

[bough]

I don't think that you will be able to do that through SQL as the "IN" keyword will go through each record in the table and check that its in that list, whichever order it is in.

The best way i can think you do it, is to run the same query and then use PHP to organise your results ie

$query = "SELECT * FROM mytable WHERE id IN (1002,1004,1001,1000, 1003)";
$result = mysql_query($query);

while ($row = mysql_fetch_assoc($result)) {
{
$array1[0] = $row[2];
$array1[1] = $row[4];
$array1[2] = $row[1];
$array1[3] = $row[0];
$array1[4] = $row[3];
}

to give you an array of the results in the order you require

Is that what you was looking to do?

[Plasma]

Use ORDER BY Field

SELECT * FROM mytable WHERE id IN (1002,1004,1001,1000, 1003)
ORDER BY FIELD(id, 1002,1004,1001,1000, 1003) DESC