CODEWITHSUNDEEP
mysqlsqlmaxmin
user9069255
user9069255

Reputation:

SELECT the Min & Max from a table with the category

I have a table with multiple columns. I need to the get min and max value from the entire table, but also display what category that min and max value are in. The column names I need are Asset_Type and Asset_Value. There are multiple (5+) asset types but I only need to show the asset type of the min value and max value. 

SELECT Asset_Type, MAX(Asset_Value), MIN(Asset_Value) 
FROM Asset
GROUP BY Asset_Type

This is what I have, but this diplays the min and max for each asset type, not just the min and max for the table.

Upvotes: 0

Views: 2255

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

There can be many asset types with the minimum value and many with the maximum value. So simply select all asset_types where the value either matches the minimum or the maximum value (which you look up in subqueries):

select distinct asset_value, asset_type
where asset_value = (select min(asset_value) from asset)
   or asset_value = (select max(asset_value) from asset)
order by asset_value, asset_type;

There are other ways to write this query, but the idea remains the same.

Upvotes: 0

slaakso
slaakso

Reputation: 9050

Considering that max value may have different Asset_type than the min value, you need to make it separate query (not taking into account here that there might be multiple Asset_types with same min/max-value.

(select 'max', Asset_Type, max(Asset_Value) as 'Asset_Value'
 from Asset
 group by Asset_Type
 order by 3 desc
 limit 1)
union all
(select 'min', Asset_Type, min(Asset_Value)
 from Asset
 group by Asset_Type
 order by 3 asc
 limit 1)

Upvotes: 1

Related Questions