Question 22140 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL: How do I GROUP BY 2 fields and return the maximum of those 2 fields in another?

Route Incident delay_minute
63 Operator 60
63 Operator 24
63 Mechanical 89
54 Operator 70
54 Sanitation 34
54 Operator 12

From the example table above, I want to return one row per route with their most common type/form of Incident. Such that it would look like this:

Route Incident
63 Operator
54 Operator

I have tried the following query, but I am unsure whether or not it returns the most common form of Incident per route:

SELECT Route
   , Incident
FROM bus_delay;

I have also attempted to use COUNT(DISTINCT) but I require the Incident type returned as string, per route:

SELECT DISTINCT Route
    , Incident
    , COUNT(Incident) AS count_incident
FROM bus_delay
GROUP BY Incident
    , Route;

How do I query such a table to return one row per Route, with that row only showing the most common form of Incident for that Route?

Tags:

3

Answers


  1. 0 
    SELECT Z.Route,Z.Incident  FROM
(
  SELECT C.Route,C.Incident,
   ROW_NUMBER()OVER(PARTITION BY C.Route,C.Incident ORDER BY (SELECT NULL))XCOL
  FROM YOUR_TABLE AS C
)Z WHERE Z.XCOL>1
    Login or Signup to reply.
  2. 0

    Use WINDOW function and CTE

    WITH CTE as(
   SELECT
      Route,    Incident,   delay_minute,
      ROW_NUMBER() OVER(PARTITION BY Route,     Inciden ORDER BY delay_minute DESC) rn
   FROM bus_delay)
SELECT
Route,  Incident,   delay_minute
FROM CTE WHERE rn = 1
    Login or Signup to reply.
  3. 0

    Try the following:

    select Route, Incident
from
(
  select Route, Incident, 
       row_number() over (partition by Route order by count(*) desc) rn
  from bus_delay
  group by Route, Incident
) T
where rn=1

    If there could be a ties in the Incident counts per route (multiple counts with the same value) and you want to return all of Incident with the maximum count then use DENSE_RANK instead of ROW_NUMBER.

    See a demo.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search