How to select the second most frequent value in column?-CodePudding

Could you please help me with this select query?

I need to select the most second frequent (duplicated) country in the column

Output should be like this: IT

CodePudding user response：

There might be shorter and simpler queries, but this one should work on every DB and prevent issues like the key words LIMIT, TOP etc. can't be used on all different DB's.

SELECT name FROM Countries
GROUP BY name
HAVING COUNT(name) = (SELECT MAX(a.amount) AS amount
FROM (SELECT name, COUNT(name) AS amount
FROM Countries
GROUP BY name) a
WHERE amount < (SELECT MAX(b.amount) FROM
(SELECT name, COUNT(name) AS amount
FROM Countries
GROUP BY name)b));

This query will find step by step the second frequent value of your table using sub queries.

CodePudding user response：

SELECT name
FROM Countries
GROUP BY name
ORDER BY count(name) DESC
LIMIT 1,1;

CodePudding user response：

Hello you can use this also

with flo as (
select country , count(*)as cate, dense_rank()over( order by count(*)  desc)as rnk
from yourtable
group by country)
select country from flo 
where rnk=2

CodePudding user response：

i wrote now this query and it worked well too :)

SELECT name FROM Countries GROUP BY name HAVING count(name) = (
SELECT a.amount from (
    SELECT COUNT(name) as amount FROM countries GROUP BY name ORDER by amount DESC limit 2,1) a  )