I have a table called email from which I fetch email. The table has email in this order
Now If I make this query
SELECT * FROM email WHERE (email IN ( '[email protected]' ,'[email protected]' , '[email protected]' )) AND email!='' LIMIT 3
I get this
but I want to get result on the basis of the arguments in the IN clause
If you notice in the IN I mentioned [email protected] first,
I dont think order by clause will help me here
Any other way of solving this issue?
One possible approach is using FIELD() MySQL function:
SELECT *
FROM email
WHERE FIELD(email, '[email protected]', '[email protected]', '[email protected]') <> 0
ORDER BY FIELD(email, '[email protected]', '[email protected]', '[email protected]');
As FIELD returns 0 if its first argument isn't equal to any other, FIELD(str, 'aaa', 'bbb'...) <> 0 clause is roughly equivalent to str IN ('aaa', 'bbb').
LIMIT actually, have you checked it?FIELD is used both for ordering (obviously) and for filtering out the unwanted strings from the result set (in the previous edition I've done the same with LIMIT 3 clause).
FIELD will use an index on the email column, or if this amounts to a seq. scan.