Find the city which a name most repeated

user_16213900 0
Viewed 53

I have two tables:

table_1: id, name (DEFAULT 'JACK')

table_2: id, city

I link them with "id" and insert some data into them like:

INSERT INTO table_1 VALUES(1, DEFAULT);
INSERT INTO table_1 VALUES(2, DEFAULT);
INSERT INTO table_1 VALUES(3, DEFAULT);
INSERT INTO table_1 VALUES(4, DEFAULT);


INSERT INTO table_2 VALUES(1, 'Paris');
INSERT INTO table_2 VALUES(2, 'Paris');
INSERT INTO table_2 VALUES(3, 'Paris');
INSERT INTO table_2 VALUES(4, 'Berlin');

I added 3 JACK into Paris above, I need to traverse it somehow to get the city that has the most JACK name in it. How can I do it?

select postgresql sql
2 Answers

You could use rank to rank the cities by number of jacks and then take the top one:

SELECT city
FROM   (SELECT city, RANK() OVER (ORDER BY cnt DESC) AS rk
        FROM   (SELECT   city, COUNT(*) AS cnt
                FROM     t1
                JOIN     t2 ON t1.id = t2.id
                WHERE    name = 'Jack'
                GROUP BY city
               ) x
       ) y
WHERE  rk = 1
user_24227760 
SELECT t1.id, t1.name, t2.city, COUNT(t1.name) FROM table_1 t1
JOIN table_2 t2 ON t1.id = t2.id
GROUP BY t2.city, t1.name;
user_15636960
Related