SQL Query to find ID's with as many hits as possible from an Array.

Question

Hi everyone, English is not my native language so i'll try to explain as good as i can.

I have a table with ID's in this table i also have account numbers.

something like this:

ID  ACCOUNT
1   1000
1   1001
1   1002
2   1000
2   1001
3   1003

then i have an array (Posted from a form, like name="array[0]",name="array[1]",name="array[2]"...) with these account numbers:

1000
1001
1002
1003

Now i want the query to get the ID's with the most "hits":

1 = 3 out of 4
2 = 2 out of 4
3 = 1 out of 4

so with this query i need to get:

1 for 1000, 1001 and 1002
3 for 1003

Kind off confusing? :)

Any suggestions?

Answer 1

SELECT COUNT(ID) AS hits FROM table1 GROUP BY ACCOUNT WHERE ACCOUNT IN (1000, 1001, 1002) ORDER BY hits DESC