Skip to content
Advertisement

Is there an SQL statement that can return maximum values of a column from a table for each of the groups resulting from groupby another column?

I would like to know if we can generate a single SQL statement that;

  1. Groups rows in a relational table to n groups based on a given column col_1
  2. Reports the maximum value for each of the groups based on another column col_2.


e.g. A dataset has many makes of cars and their respective costs and so we need to have a report on the max cost for each category of a car make? like

Make, Cost
Range Rover, $50
Toyota, $30
Merc, $20

Where $50, $30 and $20 are the maximum values of the column cost for each of the car_make groups RangeRover, Toyota and Merc respectively.

I am imagining an SQL statement that would yield same result as df.groupby(['Mt'], sort=False)['count'].max() in pandas dataframes.

Advertisement

Answer

its possible with:

select col_1, max(col_2)
from table_name
group by col_1
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement