How to get the Max value from the subquery in SQL?-CodePudding

SELECT *
FROM passenger
WHERE (
        SELECT COUNT(PassengerId) as total_passengers, Sex 
        FROM passenger
        WHERE SibSp > 0
        GROUP BY 2
        ORDER BY 1)

I have to determine whether the count of male or female was more in my dataset who had atleast 1 SibSp

When I execute the subquery I get Male and Female count as required. How do I choose the max count from my subquery?

Please help

CodePudding user response：

You don't need a subquery. Just use the grouped query and add LIMIT 1 to get the first row, which has the max count.

SELECT Sex, COUNT(*) AS total_passengers
FROM passenger
WHERE SibSp > 0
GROUP BY Sex
ORDER BY total_passengers DESC
LIMIT 1