AXAI AXAI - 3 years ago 159
MySQL Question

SQL IN Operator Selection ORDER?

For the SQL IN Operator

SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');


If it is going to select the rows with all values matched first? I do realize that the query can be translated into

SELECT * FROM Customers
WHERE Country = Germany OR Country = France OR Country = UK;


But let's say I have this table

[id - customer - Country]
[ 1 - CUS1 - Germany]
[ 2 - CUS1 - UK ]
[ 3 - CUS2 - UK ]
[ 4 - CUS3 - Germany]
[ 5 - CUS1 - France ]
[ 6 - CUS3 - UK ]
[ 7 - CUS2 - France ]
[ 8 - CUS2 - Germany]


The statics would be

CUS1 = [Germany, UK, France];
CUS2 = [UK, France, Germany];
CUS3 = [Germany, UK];


For
IN ('Germany', 'France', 'UK');
What i expect to get is
[CUS1, CUS2, CUS3];


But for
IN ('Germany', 'UK');
What i expect to get is
[CUS3, CUS1, CU2];


But I still keep getting
[CUS1, CUS2, CUS3];


What I'm trying to do is get the very matched values first, so if I have
IN ('Germany', 'UK');
I get
CUS3
first because it has the exact number and countries then
CUS1
because its first two countries are the exact same too, But with extra country,
CUS2
At last because the matched countries are reversed with extra country.

Answer Source

If you want to count the number of matches, then a single IN is not sufficient. You need to combine data from multiple rows -- and that suggests aggregation.

You can get the customers by doing:

SELECT c.customer
FROM Customers c
WHERE c.Country IN ('Germany', 'France', 'UK')
GROUP BY c.customer
ORDER BY COUNT(c.County) DESC;

(Note: If you can can have duplicates, then use COUNT(DISTINCT) in the HAVING clause.)

If you need the original rows, then use IN, EXISTS, or JOIN to get them.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download