CODEWITHSUNDEEP
mysqlgroup-by
Furqan Tariq
Furqan Tariq

Reputation: 73

selecting top row of each repeating tuple according to some criteria (MYSQL)

I have an example table like this:

Month City Person
8 LHR ABC
10 BEIJING BCS
11 NY JJJ
11 VENICE hghg
11 VENICE KKK
12 NY aa
12 ORL abc
12 ORL bbc

So what I want to achieve is see the city in a specific month with the most number of people visiting

Like the output should be: 

12 ORL  
11 VENINCE  
10 BEIJING  
8 LHR

I have tried grouping it like 

SELECT month, city , count(*) AS 'no of people visiting'     
FROM table
GROUP BY month, city

This table does tell me which city and month location had how many people
visiting but I cannot extract the the top most month and city combination
with respect to a certain month.

Updated Query (with error)

SELECT *  
FROM   
( SELECT monthname(reservation.PickupDate), location.LocationName, COUNT(*) AS count
FROM reservation  NATURAL JOIN location
WHERE reservation.pickupdate >= DATE_ADD(NOW(), INTERVAL - 3 MONTH)
GROUP BY month(reservation.PickupDate), location.LocationName) AS t1
WHERE NOT EXISTS (SELECT 1
FROM reservation R1 NATURAL JOIN location L1
WHERE  monthname(R1.PickupDate) = monthname(t1.PickupDate)
GROUP BY month(R1.PickupDate), L1.LocationName)

Upvotes: 2

Views: 41

Answers (1)

Amir Rahimi Farahani
Amir Rahimi Farahani

Reputation: 1590

Starting from your query, you just need to eliminate those rows having another city with more visitors on that month:

SELECT * 
FROM
  (SELECT `month`, city, count(*) AS cnt 
  FROM `table` 
  GROUP BY `month`, city) t1
WHERE NOT EXISTS(SELECT 1 
  FROM `table` t2 
  WHERE t2.`month` = t1.`month`
  GROUP BY `month`, city 
  HAVING count(*) > t1.cnt)

Upvotes: 1

Related Questions