how to sort 'WHERE field IN (x, y, z)'?

[Guest]

hi all
the result of 'WHERE field IN (x, y, z)' dosn't take care of the order of x,
y, z.
is there a way to do exactly this: return records in a given order (not ASC
or DESC but x, y, z? i was surprised to see, that something like

SELECT * FROM table WHERE field IN (4, 23, 7, 79) ORDER BY field IN (4, 23,
7, 79)

does not cause an error, but it also doesn't return the records in the
desired order.
i know how to work around the problem, but is there a direct way?

pp

 

[Guest]

Try this, you don't need the in () in the sort

SELECT * FROM table WHERE field IN (4, 23, 7, 79) ORDER BY field

 

[Guest]

That won't sort by the order of items in the in set.

I don't think there is a direct way of doing this in a single SQL statement.

 

[Guest]

SELECT *
FROM MyTable
WHERE FieldName In (1,2,3)
ORDER BY FieldName

that would work, and you can try it

 

[Rick Brandt]

SELECT *
FROM MyTable
WHERE FieldName In (1,2,3)
ORDER BY FieldName

that would work, and you can try it

No, what the OP wants (apparently) from...

SELECT *
FROM MyTable
WHERE FieldName In (6, 2, 9)

....is to see all of the 6s first, then the 2s, then the 9s.

You would need to add a calculated field...

SELECT *
FROM MyTable
WHERE FieldName In (6, 2, 9)
ORDER BY Switch(FieldName=6,1,FieldName=2, 2, FieldName=9,3)

 

[Rick Brandt]

SELECT *
FROM MyTable
WHERE FieldName In (1,2,3)
ORDER BY FieldName

that would work, and you can try it

No, what the OP wants (apparently) from...

SELECT *
FROM MyTable
WHERE FieldName In (6, 2, 9)

....is to see all of the 6s first, then the 2s, then the 9s.

You would need to add a calculated field...

SELECT *
FROM MyTable
WHERE FieldName In (6, 2, 9)
ORDER BY Switch(FieldName=6,1,FieldName=2, 2, FieldName=9,3)

 

[Rick Brandt]

SELECT *
FROM MyTable
WHERE FieldName In (1,2,3)
ORDER BY FieldName

that would work, and you can try it

No, what the OP wants (apparently) from...

SELECT *
FROM MyTable
WHERE FieldName In (6, 2, 9)

....is to see all of the 6s first, then the 2s, then the 9s.

You would need to add a calculated field...

SELECT *
FROM MyTable
WHERE FieldName In (6, 2, 9)
ORDER BY Switch(FieldName=6,1,FieldName=2, 2, FieldName=9,3)

 

[Guest]

Rick,

As a followup: Is there a generic way to accomplish this? Suppose for
example that the set was much larger- say 10 or more values; that ORDER BY
starts to get pretty hairy. Or that the values change between runs. Or the
size of the set changes from run to run.

Just trolling here.

 

[Guest]

I see now, thank you.
So you can use the switch or iif for the sort, but I agree it's better to
use the switch.

ORDER BY IIf([A]=6,1,IIf([A]=2,2,IIf([A]=9,3))

 

[Rick Brandt]

Rick,

As a followup: Is there a generic way to accomplish this? Suppose for
example that the set was much larger- say 10 or more values; that
ORDER BY starts to get pretty hairy. Or that the values change
between runs. Or the size of the set changes from run to run.

Just trolling here.

Doubtful and any time you apply criteria to an expression it is very
inefficient because indexes cannot be used so it's not a strategy I would
recommend except on fairly small sets of data.

One strategy that solves both problems is to give the user a form to enter
his criteria into. That form is actually populating a two field table. One
field that is the criteria value and the other is a numeric field that is
incremented as they make their entries. Then the query uses a join to that
table (eliminates the inefficient IN clause) and can sort on the incremented
number field (solves the sorting issue).

Problem with the above is that it would be awkward to manage in a multi-user
situation. I suppose a third field that included a UserID could solve that.
You would also need something that cleared the filter table after each use
(for the current user only of course).