[vox-tech] yet another SQL question...

[Jay Strauss]

Dylan Beaudette wrote:
> Hi everyone, 
> 
> I am trying to accomplish in SQL (the MySQL dialect to be exact), what may 
> only be possible with an integrated approach... but I thought that I would 
> ask:
> 
> I have a single table, called 'component'. There are records in this table 
> that represent components of a larger unit. Each record is identified with a 
> key linking it to the larger unit (mukey), and has a column that defines the 
> percent of the larger unit that this component represents (comppct_r). There 
> are other categorical attributes associated with the components as well. I 
> would like to make a table that displays the dominant component (i.e. 
> comppct_r is the largest for a given larger unit) and associated attributes 
> for each larger unit.
> 
> here is an example query:
> select component.mukey, component.comppct_r, component.taxorder, 
> component.taxsuborder, component.taxgrtgroup 
> from component limit 20;
> +--------+-----------+-------------+-------------+--------------+
> | mukey  | comppct_r | taxorder    | taxsuborder | taxgrtgroup  |
> +--------+-----------+-------------+-------------+--------------+
> | 467166 |        90 | Inceptisols | Xerepts     | Haploxerepts |
> | 467165 |        30 |             |             |              |
> | 467165 |        20 | Entisols    | Orthents    | Xerorthents  |
> | 467165 |        35 | Entisols    | Orthents    | Xerorthents  |
> | 467164 |        85 | Entisols    | Orthents    | Xerorthents  |
> | 467163 |        85 | Entisols    | Orthents    | Xerorthents  |
> | 467160 |        30 | Mollisols   | Xerolls     | Haploxerolls |
> | 467160 |        60 | Alfisols    | Xeralfs     | Haploxeralfs |
> | 467159 |        30 |             |             |              |
> | 467159 |        20 | Entisols    | Orthents    | Xerorthents  |
> | 467159 |        35 | Entisols    | Orthents    | Xerorthents  |
> | 467158 |        85 | Alfisols    | Xeralfs     | Haploxeralfs |
> | 467157 |        85 | Inceptisols | Xerepts     | Haploxerepts |
> | 467156 |        85 | Alfisols    | Xeralfs     | Haploxeralfs |
> | 467155 |        85 | Inceptisols | Xerepts     | Calcixerepts |
> | 467154 |        85 | Mollisols   | Xerolls     | Argixerolls  |
> | 467153 |        30 | Inceptisols | Xerepts     | Haploxerepts |
> | 467153 |        20 |             |             |              |
> | 467153 |        35 | Alfisols    | Xeralfs     | Haploxeralfs |
> | 467152 |        85 | Alfisols    | Xeralfs     | Natrixeralfs |
> +--------+-----------+-------------+-------------+--------------+
> 
> I am able to accomplish what I am interested with an intermediate step, but if 
> there is a single step it would be nice.
> 
> Here is my idea for a 2 step solution to the above:
> 1. create table comp_max select component.mukey, max(comppct_r) from component 
> group by component.mukey
> 
> 2. join the records from the component table based on mukey and the max_pct 
> value
> 
> ...perhaps this can be done with a subselect..?
> 
> thanks in advance for any ideas!
> 

In oracle it would be like (maybe it's the same in MySql)

select * from component
where (mutkey,compct_r) = (
select mutkey, max(compct_r)
   from component
  group by mutkey)