CODEWITHSUNDEEP
mysql
denny
denny

Reputation: 2254

find maximum matches rows and count it in mysql

i have a table name 'room' with room_id and rental.

mysql> select * from room;

+---------+--------+
| room_id | rental |
+---------+--------+
|       1 | 2000   |
|       2 | 1890   |
|       3 | 1832   |
|       4 | 1833   |
|       5 | 1850   |
|       6 | 1700   |
|       7 | 2100   |
|       8 | 2000   |
|       9 | 2000   |
|      10 | 2000   |
+----------+--------+
10 rows in set (0.00 sec)

i tried find maximum matches rows and count into number from rental column.

mysql> select count(*),rental from room group by rental having count(*) >1;

+----------+--------+
| count(*) | rental |
+----------+--------+
|        4 | 2000   |
+----------+--------+
1 row in set (0.08 sec)

but my problem is i just want only one max number from rental which has max matches values and output like above.in above query will take a condition like count(*) > 1.but i want to check all rows from rental column instead of it condition.

Upvotes: 0

Views: 122

Answers (1)

Mahesh Madushanka
Mahesh Madushanka

Reputation: 2988

USE ORDER BY AND LIMIT 1

select count(*) as cnt,rental from room group by rental order by cnt DESC limit 1;

Upvotes: 2

Related Questions