CODEWITHSUNDEEP
mysqlsql
AXAI
AXAI

Reputation: 706

ORDER values by the Highest match in IN Clause

    talb1                          tabl2
[id   -   col1]               [tid   -   col1]
[1    -    aa1]               [1     -      c]
[2    -    ba2]               [1     -      b]
[3    -    ca3]               [1     -      a]
[4    -    da4]               [1     -      d]
                              [2     -      c]
                              [2     -      a]
                              [2     -      b]
                              [3     -      b]
                              [3     -      a]
                              [4     -      a]

in the following query

$query = "
SELECT a.id 
FROM a.tabl1 
LEFT JOIN tabl2 b ON a.id = b.tid 
WHERE b.col1 IN ('a', 'b', 'c', 'd')
GROUP BY a.id
";

I'm trying to get the highest IDs matched within the IN Clause, I get the values, But they're never ordered,

I want to order them by the highest id having the highest match

for example

ID[4] = match['a', 'b', 'c', 'd'];
ID[2] = match['a', 'b', 'c'];
ID[3] = match['a', 'b'];
ID[1] = match['a'];

so i tried

ORDER BY COUNT(b.col1) DESC but it is clearly won't do anything as i want it to happen, How exactly to order a query like this?

Note: If i've selected a, b, c only inside the IN Clause i get ordered values, But if selected a, b, c, d (All letters) i get random values.

Upvotes: 1

Views: 45

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

You need to GROUP BY in order to useORDER BY COUNT(...):

SELECT a.id , COUNT(b.col1) AS cnt
FROM tabl1 a
LEFT JOIN tabl2 b 
  ON a.id = b.tid 
 AND b.col1 IN ('a', 'b', 'c', 'd')
GROUP BY a.id
ORDER BY cnt DESC;

Rextester Demo

Upvotes: 1

Related Questions