Get the highest Matched output in result I have a table like this



Now my requirement is like below:-

I have to fetch all Content ID related to User Id-31. Here the matching content id are 225,178. I am able to retrieve this. Now I want to do the following:-

I want to get all user id whose Content ID matches with Content ID of 31 in Decreasing order. It means that if all content id matches then it will appear first otherwise  it will appear last. Here User ID 20 matches 2 times,User Id 19 matches 1, User ID 1 also matches 1 times.  So Result set will be User ID 20,19,1. Now I want to achieve this in Mysql as well as db_select in Drupal 7.
It's something like this ... Since I dont have your exact table in my database it could be slightly wrong. But this is 80% or 99% what you're asking.

$query = db_select('my_table', 't')
    -&gt;fields('t', array('UserID'))
    -&gt;condition('contentID', 31)
    -&gt;groupBy('UserID')
    -&gt;orderBy(2, 'DESC');
$query-&gt;addExpression('COUNT(t.UserID) AS num_items');
$results = $query-&gt;execute();

// dpm() is in the Devel module.
foreach ($results as $record) { dpm($record); }


EDIT:

The final query that worked was:

$query = db_select('flag_content','fcnt')
-&gt;fields('fcnt', array('uid'))
-&gt;condition('fcnt.content_id', array(178,225), 'IN')
-&gt;groupBy('fcnt.uid')
-&gt;orderBy('num_items', 'DESC');
$query-&gt;addExpression('COUNT(*)' , 'num_items');