Making a MAX() query from a subquery with COUNT()

Question

Need to Show the Name of the boat which made the most trips, so i made a query that counts the trips:

SELECT B.IdBoat, COUNT(T.IdTrip)
FROM Trip T INNER JOIN Boat B ON T.IdBoat=B.IdBoat
GROUP BY B.IdBoat

Now I need to show the name of the one with the MAX trips, how do I use that query as a subquery, without using the ORDER BY DESC and TOP 1 but using MAX? Currently got:

SELECT B.Name
FROM Trip T INNER JOIN Boat B ON T.IdBoat=B.IdBoat
WHERE B.IdBoat = MAX( the sub query above)

also tried

SELECT B.Name, T.IdTrip
FROM Boat B INNER JOIN Trip T ON B.IdBoat=T.IdBoat
WHERE B.IdBoat IN (
SELECT MAX(T.NTrips) FROM 
    (SELECT B.IdBoat AS [IdBoat], COUNT(T.IdTrip) AS [NTrips]
    FROM Trip T INNER JOIN Boat B ON B.IdBoat=T.IdBoat
    GROUP BY B.Boat) T
    GROUP BY T.IdBoat)

The above returned the full count of 3 on the name of the boat instead of the correct 2.

I've tried googling and searching about said problem on stackoverflow and others but can't adapt their solution to my query, any help is good help.

Thank you.

edit 1. As asked, I'll provide some data as to help understand the problem better

Table Boat:

IdBoat | Name
1      | 'SS Sparrow'
2      | 'SS AndaNoMar'

Table Trip

IdTrip | IdBoat
1      | 1
2      | 1
3      | 2

Subquery 1 (COUNT)

IdBoat | NTrips
2      | 1
1      | 2

It is always a good idea to have some sample data with the expected output. That makes it easier to understand. — Tony, Jun 08 '19 at 19:31
Understood, Tried adding sample data as to help understand the problem better, didn't know how to style it better though, I'm sorry. — yep, Jun 08 '19 at 19:39
Look at this, if it can help you https://stackoverflow.com/questions/2436820/can-i-do-a-maxcount-in-sql — Mova, Jun 08 '19 at 19:42
Why not just use `TOP 1` and `ORDER BY COUNT(T.IdTrip) DESC`? — MatBailie, Jun 08 '19 at 19:47
@MatBailie Because if by chance one boat has the same ammount of trips as another boat, it'll only show 1 of the boats, and with the max() query it'll show all of the boats with the max value. Or that's my understanding of it. — yep, Jun 08 '19 at 19:54
Possible duplicate of [SQL - Displaying entries that are the max of a count?](https://stackoverflow.com/questions/5159928/sql-displaying-entries-that-are-the-max-of-a-count) — Mova, Jun 08 '19 at 20:03

score 2 · Accepted Answer · edited Jun 08 '19 at 20:14

2

You can do:

with
x as (
  select
    b.idBoat,
    b.Name,
    count(*) as cnt
  from trip t
  join boat b on b.idBoat = t.idBoat
  group by b.idBoat, b.Name
),
m as (
  select max(cnt) as max_cnt from x
)
select 
  x.*
from x
join m on m.max_cnt = x.cnt

edited Jun 08 '19 at 20:14

Mova

928
1
6
23

answered Jun 08 '19 at 19:44

The Impaler

45,731
9
39
76

I think you also need to add b.Name in group by clause. – Mova Jun 08 '19 at 19:49
And a comma in the SELECT block – MatBailie Jun 08 '19 at 19:50
Sorry for the delay, took me some time to adapt/understand the code but it did work, I've never used the WITH command up until now and didn't know this was an option. Thanks for the code I'm going to try and understand it better now. – yep Jun 08 '19 at 20:11
@MOI No, `b.Name` is not needed in the `GROUP BY` clause if `idBoat` is the primary key of the table. The SQL standard allows "directly dependent columns" not to be present in the `GROUP BY` clause. – The Impaler Jun 08 '19 at 21:21
@TheImpaler Will it work in mssql or just mysql? I tried as you said, But it did not work for me in mssql. I am not able to find it on msdn. So, If you have a link where I can read the standard about the group by clause as you said, please share it. – Mova Jun 09 '19 at 07:01
@MOI You are right. I didn't know SQL Server didn't implement this supposedly standard feature. I tried it in SQL Server 2014 and 2017 to no avail. – The Impaler Jun 09 '19 at 15:02

score 1 · Answer 2 · answered Jun 08 '19 at 20:14

SELECT
  B.IdBoat,
  B.Name,
  T.Trips
FROM
  Boat  AS B
INNER JOIN
(
  SELECT
    IdBoat,
    COUNT(*)  AS Trips,
    RANK() OVER (PARTITION BY IdBoat
                     ORDER BY COUNT(*) DESC
                )
                  AS TripsRank
  FROM
    Trip
  GROUP BY
    IdBoat
)
  AS T
    ON T.IdBoat = B.IdBoat
WHERE
  T.TripsRank = 1

score 0 · Answer 3 · answered Jun 08 '19 at 22:28

A better method than either of the other two answers is to use ORDER BY:

SELECT TOP (1) B.IdBoat, B.Name, COUNT(T.IdTrip) as cnt
FROM Trip T INNER JOIN
     Boat B 
     ON T.IdBoat = B.IdBoat
GROUP BY B.IdBoat, B.Name
ORDER BY cnt DESC;

There is no need for subqueries or CTEs or window functions.

If you want ties, then you can use TOP (1) WITH TIES.

Making a MAX() query from a subquery with COUNT()

3 Answers3