CODEWITHSUNDEEP
mysql
Adam
Adam

Reputation: 20882

MySQL IN Statement + return row with most matches

I have the following SQL Query - note: the IN words change for different queries:

SELECT a.pid,a.city,a.countryCode,b.zipEnabled,b.english 
FROM geoWorld AS a
JOIN geoCountry AS b ON a.countryCode=b.countryCode
WHERE a.city IN ("free","dating","donvale","australia");

I get 3 returns.

2 match 'australia' and 1 matches 'donvale' and 'australia'.

Is there a way for me to return or order by the highest matches?

I can manipulate the results with PHP but would be great if I could do this with SQL alone.

cheers

Upvotes: 1

Views: 87

Answers (3)

seahawk
seahawk

Reputation: 1912

Use limit to get the top record:

SELECT a.pid,a.city,a.countryCode,b.zipEnabled,b.english 
FROM geoWorld AS a
JOIN geoCountry AS b ON a.countryCode=b.countryCode
WHERE a.city IN ("free","dating","donvale","australia")

Group by a.city Order by count(a.city) desc
limit 1 ;

Upvotes: 1

kinjelom
kinjelom

Reputation: 6450

Use "group by":

select a.pid,a.city,a.countryCode, b.zipEnabled,b.english, a.c from (    
    SELECT a.pid,a.city,a.countryCode, count(*) c 
    FROM geoWorld AS a 
    JOIN geoCountry AS b ON a.countryCode=b.countryCode
    WHERE a.city IN ("free","dating","donvale","australia")
    group by a.pid,a.city,a.countryCode
) AS a JOIN geoCountry AS b ON a.countryCode=b.countryCode
order by c desc

or

SELECT a.pid,a.city,a.countryCode,b.zipEnabled,b.english, 
  (select count(*) from geoCountry c where c.countryCode = a.countryCode) ct
FROM geoWorld AS a
JOIN geoCountry AS b ON a.countryCode=b.countryCode
WHERE 
  a.city IN ("free","dating","donvale","australia")
order by ct desc

or if you "need to return the row that matches 2 values in the IN Statement" use having statement, e.g.:

select a.pid,a.city,a.countryCode, b.zipEnabled,b.english, a.c from (    
    SELECT a.pid,a.city,a.countryCode, count(*) c 
    FROM geoWorld AS a 
    JOIN geoCountry AS b ON a.countryCode=b.countryCode
    WHERE a.city IN ("free","dating","donvale","australia")
    group by a.pid,a.city,a.countryCode
    having c=2
) AS a JOIN geoCountry AS b ON a.countryCode=b.countryCode
order by c desc

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Hmmm, is this what you mean?

SELECT w.countryCode, count(*)
FROM geoWorld w JOIN
     geoCountry c
     ON w.countryCode = c.countryCode
WHERE w.city IN ('free', 'dating', 'donvale', 'australia')
GROUP BY w.countryCode
ORDER BY count(*) DESC;

Upvotes: 0

Related Questions