I would like to know if we can generate a single SQL statement that; Groups rows in a relational table to n groups based on a given column col_1 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

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;

Groups rows in a relational table to n groups based on a given column col_1
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.

Answer

its possible with:

select col_1, max(col_2)
from table_name
group by col_1

Advertisement

Answer