How to select the rows with max certain column value?-CodePudding

I have a table with several rows and two columns, it is like this.

 station_id | count
------------ -------
        184 |     7
         87 |    31
         71 |    29
         68 |     7
         51 |    65
        146 |    22
         80 |    37
         70 |    18
         52 |    12
        132 |    21
         84 |    65
        176 |    29
         92 |    21
        101 |    28
         69 |     4
        180 |    32
         97 |    32
        108 |     9
        156 |    50
         59 |    10
        135 |    24
         65 |    20
        127 |    44
        124 |    20
         98 |    28
        178 |    65

I want to select the rows with max count value. If there is only one row with the max value, I know I can select and output the row (station_id and count both should be selected out) by

select station_id, count(*) count
from bus_lines
group by station_id
order by count desc limit 1;

But I don't know use what sql statement to select it if there is several rows with the max value. It would be great if you can offer me the sql(maybe postgresql) statement.

CodePudding user response：

You might want to use RANK() or DENSE_RANK()

SELECT * FROM (select station_id, count(*) count,
dense_rank() over(order by count desc) as count_rank
from bus_lines
GROUP BY station_id
ORDER BY count_rank
) t

CodePudding user response：

Switch from LIMIT to FETCH FIRST 1 ROW WITH TIES to include all rows having the max count:

select station_id, count(*) count
from bus_lines
group by station_id
order by count desc FETCH FIRST 1 ROW WITH TIES