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

user_11619426 0
Viewed 299

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
sql-server sql
3 Answers

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
user_64361910 
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
Dems_533410

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.

user_11440350
Related