CODEWITHSUNDEEP
mysqlsqlcount
grooove
grooove

Reputation: 85

SQL max value form count()

I've got a following query:

SELECT BusID, count(BusID) as 'NoofConnections'
FROM mytable
Group By BusID
Order by BusID

And I get following table:

BusID   NoofConnections
=========================
1       6
2       6
4       3
5       2
3       1

And I will to select just maximum from NoofConnections, so in this case LIMIT 1 doesn't work, so we have two equal values. Like this:

 BusID   NoofConnections
=========================
1            6
2            6

Any ideeas?

Upvotes: 0

Views: 45

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a subselect 

  select * from (
    SELECT BusID, count(BusID) as NoofConnections
    FROM mytable
    Group By BusID

  )  t1 
  where t1.NoofConnections = (  select max(NoofConnections)
      from (
      SELECT BusID, count(BusID) as NoofConnections
      FROM mytable
      Group By BusID
      )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269573

You can use a subquery:

SELECT BusID, COUNT(*) as NoofConnections
FROM mytable
GROUP BY BusID
HAVING COUNT(*) = (SELECT COUNT(*)
                   FROM mytable
                   GROUP BY BusID
                   ORDER BY COUNT(*) DESC
                   LIMIT 1
                  )
ORDER BY BusID;

Upvotes: 2

Related Questions