ORDER BY on IN operator

[Bertus Dam]

I have the following query:

SELECT id, field1, field2 FROM tableX WHERE id IN (8,2,34,16)

I want the result of this query to be sorted on (8,2,34,16). So I want the
record with id 8 first, the record with id 2 second, etc. Is this possible?


Bertus

 

[Tom Ellison]

Dear Bertus:

I suggest you could map these values into another column that is in
sequence:

8 1
2 2
34 3
16 4

You could do this by adding a column to an existing table (if you have
one that contains these 4 values) or you could create another table.

If this column is called Seq then:

SELECT tableX.id, tableX.field1, tableX.field2
FROM tableX
INNER JOIN OtherTable ON OtherTable.id = tableX.id
WHERE OtherTable.Seq IS NOT NULL
ORDER BY OtherTable.Seq

Seq would be null for any rows other than these 4.

I have the following query:

SELECT id, field1, field2 FROM tableX WHERE id IN (8,2,34,16)

I want the result of this query to be sorted on (8,2,34,16). So I want the
record with id 8 first, the record with id 2 second, etc. Is this possible?


Bertus

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts